SELECT from inline CSV values in MS SQL

Question

I occasionally get one-off requests to modify some data, and the request comes with some values in CSV. Is there a way I can treat arbitrary CSV as a table or row source quickly and easily, even as inline data?

EG, can I do something like:

SELECT * FROM SomeSpecialCommand('
147074,13
153987,16
147075,3
176405,16
176437,25
176436,14
176406,14
176439,13
176407,13
') as MyInlineCSV

What prompted this was a request to modify inventory levels for ~100 items, and I was given a CSV with a SKU number and quantity (like above).

Answer 1

You would want to make use of something called a user-defined table-valued function. Basically, you'll create a function that takes a single nvarchar(max) parameter and creates a table that is returned from the CSV input. There are literally dozens of approaches you could take here (using a numbers table, using an XML type, using loops, etc., etc.), but this has been thoroughly documented and tested by some smart Sql guys, so I'll just point you there instead. Be sure to read through the comments section as well, as there are alternative solutions and test results for different data sizes, environments, etc. - just pick the one that works best for you.

Answer 2

Unfortunately, you can't do this. You could create a linked server that points to an excel file. You could use this as your 'utility' linked server and save random files as the proper .xls file and you'll be able to select from the sheet.

Answer 3

Does it have to be using sql-server?

There's DBD::CSV for perl which would solve simple modifications that don't require pulling data from other tables already in your database.