SQl: Update Table from a text File

问题:

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.

回答1:

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. :)

回答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.

回答3:

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.