SQl:从文本文件更新表 [英] SQl: Update Table from a text File

问题描述

这是我必须做的:

我有一个包含 3 列的文本文件:PID、X、Y.

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

现在我的数据库中有两个表:

Now I have two tables in my database:

• 表1包含4列:UID, PID, X, Y
• Table 2 包含多列，需要的是UID, X, Y

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

我需要用相应的 X 和 Y 值更新表 2.

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

我认为我们可以使用 BULK INSERT 来更新 table 1，然后使用一些 WHILE 循环或其他东西.

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.

推荐答案

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

这篇关于SQl:从文本文件更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！