问题描述
这是我必须做的:
我有一个包含 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. :)