asked on

TSQL Update Array Possible?

I'm trying to update a TSQL database with a single UPDATE query, but with potentially many pieces of data.

Functionally, it should produce the equivalent of this:

UPDATE Table_Name
SET Cost = 1.00
WHERE SKU = 123456

UPDATE Table_Name
SET Stock = 2
WHERE SKU = 123456

Open in new window

...but with the ability to feed in hundreds or thousands of similar pieces of information, all in a single update command.

Is something like this possible or feasible?

Anthony Perkins

Is this what you mean:

UPDATE Table_Name
SET Cost = 1.00, Stock = 1
WHERE SKU = 123456

Open in new window

appari

>>...but with the ability to feed in hundreds or thousands of similar pieces of information
what exactly are you trying to do ? are you trying to import data from some other table or file to existing table? more information will help.

Shaun Kline

If you mean that your information will be coming from another data source, you can use the FROM clause in the UPDATE statement:

UPDATE Target_Table
SET Cost = Source_Table.Cost,
       Stock = Source_Table.Stock,
....
FROM Source_Table
WHERE Target_Table.SKU = Source_Table.SKU

Open in new window

ASKER CERTIFIED SOLUTION

deviprasadg

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

deighton

you can send data to a stored procedure as XML, prepare an XML document with

EXEC sp_XML_preparedocument

then via the OPENXML command, use the XML as a table