SQl: Update Table from a text File
Solution 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. :)
Solution 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.
Related videos on Youtube
Spandan
Patience is above Perfection . Find me at : LinkedIn
Updated on September 27, 2022Comments
-
Spandan over 1 year
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 beingUID, X, Y
I need to update
Table 2
with corresponding X and Y values.I think we can use
BULK INSERT
for updatingtable 1
, then someWHILE
loop or something.But I can't figure out exact thing.