Traversing multiple CSV in SQL

Question

I have a SQL Server 2008 database. This database has a stored procedure that will update several records. The ids of these records are stored in a parameter that is passed in via a comma-delimited string. The property values associated with each of these ids are passed in via two other comma-delimited strings. It is assumed that the length (in terms of tokens) and the orders of the values are correct. For instance, the three strings may look like this:

Param1='1,2,3,4,5'
Param2='Bill,Jill,Phil,Will,Jack'
Param3='Smith,Li,Wong,Jos,Dee'

My challenge is, I'm not sure what the best way to actually go about parsing these three CSVs and updating the corresponding records are. I have access to a procedure named ConvertCSVtoTable, which converts a CSV to a temp table of records. So Param1 would return

after the procedure was called. I thought about a cursor, but then it seems to get really messy.

Can someone tell me/show me, what the best way to address this problem is?

Does the stored procedure give you an ID column in your temp table as well as a column for the actual value? — AllenG
– AllenG, Commented Aug 4, 2010 at 18:13
@AllenG, I doubt it does. It is likely just a roll your own sql server split function. It should not be that hard to rewrite to have an identity column, maybe even as an optional parameter. — buckbova
– buckbova, Commented Aug 4, 2010 at 18:23

Joe Stefanelli · Accepted Answer · 2010-08-04 18:22:47Z

5

I'd give some thought to reworking the inputs to your procedure. Since you're running SQL 2008, my first choice would be to use a table-valued parameter. My second choice would be to pass the parameters as XML. Your current method, as you already know, is a real headache and is more error prone.

answered Aug 4, 2010 at 18:22

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

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

Comments

Damian Leszczyński - Vash · Accepted Answer · 2010-08-04 18:18:36Z

1

You can use bulk load to insert values to tmp table after that PIVOT them and insert to proper one.

answered Aug 4, 2010 at 18:18

Damian Leszczyński - Vash

31k9 gold badges64 silver badges95 bronze badges

Collectives™ on Stack Overflow

Traversing multiple CSV in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related