T-SQL - Update long list of values based on long list of ids

Question

It may seem as a straight forward task, but I can't really find a good approach.

I have a long list of ids with a long list of corresponding values to update for a field (a single field)

id = 1 | field = value_1 
id = 2 | field = value_2
.......................
id = n | field = value_n

I can put the fields in 2 lists (or any other way i choose to) but i have to loop through and update each value..

What would be the best approach for this?

To add few more details: The values are in a big excel, but this is not about processing that excel, I will copy paste the list of values into.. text. I was thinking 2 long list (id1, id2,..) (value_1, value_2,...)

What client do you use for this porpose? Where that data for update comes from? — Hamlet Hakobyan, Aug 08 '16 at 09:51
I usually write a python script to generate this kind of sql then copy-paste-run. — Calvinxiao, Aug 08 '16 at 11:30
if i understood correctly, you have data and wish to convert it to sql code. if so, use excel's concatenate function and wrap your data with sql parts. then copy/paste and execute. — Doruk, Aug 08 '16 at 11:41

score 3 · Accepted Answer · answered Aug 08 '16 at 11:35

For a one time job, convert the text into a CSV or other format that is processable by bcp.exe, then import it into a temp table, do the update via a JOIN, then drop the temp table.

For a repeatable job I would us SSIS: flat file source the data or even directly Excel source, source the table, merge the two sources, apply the result back into the table.

score 1 · Answer 2 · answered Aug 08 '16 at 13:44

The selected answer is a good method, but for completeness: when this is a one-time task, and the updates all follow a simple pattern like that, it can also be effective to convert the input text directly into a series of update statements, using an Excel formula and fill down or using a text editor's replace function.

Example:

id     newvalue
1      foo
2      grok

becomes

id     newvalue    generated statement
1      foo         update dbo.mytable set field1 = 'foo'  where id = 1
2      grok        update dbo.mytable set field1 = 'grok' where id = 2

Quick and dirty, but apply with care and watch out for unexpected syntax errors.

score 0 · Answer 3 · answered Aug 08 '16 at 13:23

0

is what i did in the end, I created a temp table, I imported all the values in it and updated via a join

answered Aug 08 '16 at 13:23

AdrianD

279
4
20

T-SQL - Update long list of values based on long list of ids

3 Answers3