Search code examples
sql-servert-sqlbulkupdate

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

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

Solution

  • 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.