Update SQL table from csv on multiple columns

Question

I want a basic update procedure that updates a temporary table and orders it by PrimID and myDates, and then updates a permanent table. The data structure looks like this:

PrimID  MyDates     Price
1       1/1/2014    1
1       1/2/2014    2
2       1/1/2014    11
2       1/2/2014    12
3       1/1/2014    21
3       1/2/2014    22

The csv file looks exactly the same, just without the header column names. Here is my code thus far:

CREATE Table #TempT
(
   PrimID Int,
   myDate Date,
   myPrice Float
);

BULK
INSERT #TempT
FROM 'D:\MyWerk\SQL\TEST_dPrice_Data.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


Select * From #TempT
Order by PrimID,myDate

Drop Table #TempT

What is missing, and what I am trying to get to, is the UPDATE of the permanent table with the ordered #TempT, ordered by PrimID and then myDates(oldest to lastest). If there are PrimID & myDates data in the csv that are already in the permanent table, I want to overwrite the data in the permanent file as well. Also, is there a better way to get the data in chronological order, other than using order by?

I use SQL Server 2012.

Much appreciated.

I'm having a hard time understanding what you're after, you want to update the mydate and myprice values based on the primid, but only on price if primid and mydate are in both tables? — Hart CO
– Hart CO, Commented Sep 18, 2014 at 19:18
An order by clause is rarely if ever necessary in an update query. Depending on what you are trying to achieve, maybe the sql max() function is appropriate. — Dan Bracuk
– Dan Bracuk, Commented Sep 18, 2014 at 19:37

Ryan B. · Accepted Answer · 2014-09-22 21:35:21Z

2

Don't try to store your data in SQL tables in some kind of row order -- this is inefficient. You can sort when you query the data.

As for the insert/update behavior, a SQL merge does this quite well. After your Bulk Insert, you can execute something like this:

MERGE PermanentT AS [TARGET]
USING #TempT AS [SOURCE]
ON [TARGET].PrimId = [SOURCE].PrimId
    AND [TARGET].MyDates = [SOURCE].MyDates
WHEN MATCHED AND [TARGET].PRICE <> [SOURCE].PRICE
THEN UPDATE SET [TARGET].PRICE = [SOURCE].PRICE
WHEN NOT MATCHED
THEN INSERT (PrimID, myDate, myPrice) 
VALUES (SOURCE.PrimID, SOURCE.myDate, SOURCE.myPrice);

edited Sep 22, 2014 at 21:35

answered Sep 18, 2014 at 20:14

Ryan B.

3,6852 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Allstar Over a year ago

Thank you Greenspark, seems exactly what I need. One thing, I get "Incorrect syntax near [TARGET]." At your row, "WHEN NOT MATCHED BY [TARGET]" ?

Ryan B. Over a year ago

Oops. yeah, we don't need the "By [TARGET]" there. Also, the whole merge statement needs to end in a semi colon. I've made those changes above.

Collectives™ on Stack Overflow

Update SQL table from csv on multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related