9

Here's what I have to do :

I have a text file which has 3 columns: PID, X, Y.

Now I have two tables in my database:

  • Table 1 contains 4 columns: UID, PID, X, Y
  • Table 2 contains multiple columns, required ones being UID, X, Y

I need to update Table 2 with corresponding X and Y values.

I think we can use BULK INSERT for updating table 1, then some WHILE loop or something.

But I can't figure out exact thing.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Spandan
  • 2,128
  • 5
  • 25
  • 37
  • Not clear - you want to `BULK INSERT` the text file into `Table1` and then update `Table2` from that? Or what? Also: what are the **data types** of those columns? Can you show us a **sample text file** ? – marc_s Jun 07 '13 at 08:53
  • Please quote your version of SQL Server (you can add a further specific tag) – Paul Jun 07 '13 at 12:39

3 Answers3

10
CREATE PROCEDURE [dbo].[BulkInsert]            
(            
@PID int  ,          
@x int,          
@y int,          

)            
AS            
BEGIN            
SET NOCOUNT ON;            

declare @query varchar(max)            


CREATE TABLE #TEMP            
(            
[PID] [int] NOT NULL ,          
[x] int NOT NULL,          
[y] int NOT NULL,             

)            


SET @query = 'BULK INSERT #TEMP FROM ''' + PathOfYourTextFile + ''' WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'            
--print @query            
--return            
execute(@query)            


BEGIN TRAN;            

MERGE TableName AS Target            
USING (SELECT * FROM #TEMP) AS Source            
ON (Target.YourTableId = Source.YourTextFileFieldId)
-- In the above line we are checking if the particular row exists in the table(Table1)  then update the Table1 if not then insert the new row in Table-1.           

WHEN MATCHED THEN            
UPDATE SET             
Target.PID= Source.PID, Target.x= Source.x, Target.y= Source.y           
WHEN NOT MATCHED BY TARGET THEN            

-- Insert statement

You can use this above approach to solve your problem. Hope this helps. :)

garvit gupta
  • 301
  • 3
  • 18
2

How are you going to run it ? From a stored procedure ?

To save some performance, I would have done BULK INSERT to temp table, then insert from temp table to Table 1 & 2.

It should look like this 

INSERT INTO Table1 ( PID, X, Y)
SELECT  PID, X, Y
FROM    #tempTable

Some will tell that temp table are not good, but it really depend - if you file is big, reading it from disk will take time and you don't want to do it twice.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mzf
  • 5,210
  • 2
  • 24
  • 37
  • yes form a stored proc...i wanna know,how do you acheive step 2,insert from temp table to table 2 ? – Spandan Jun 07 '13 at 08:53
  • Better to load table 1 first, then load table 2 using table 1 as a source. Table 1 has the linking between `uid` and `pid` which is necessary for loading Table 2 anyway. –  Jun 07 '13 at 09:00
1

You don't need any loop to update table 2; all you need is insert from table 1.

Or, if you are trying to update existing rows in table 2, use an update query that joins on table 1. See this question for an example.

However, you should consider changing your database design, as it appears to be incorrect: you are storing X and Y in two places; they should only be stored in one table, and you should join to this table if you need to use them in conjunction with other data. If you did this, you wouldn't have to worry about messy issues of keeping the two tables in sync.

Community
  • 1
  • 1