What is the method to add comma-separated string data into a SQL Server table?

Data and Databases
1

I’m looking to insert a comma-separated string into a SQL Server database table. For instance, I have the following code snippet:

int editorId = 21;
string workers = '2,3,4,5';

I would like the data to be represented in the table as follows:

EditorId  Worker
----------------
  21          2
  21          3
  21          4
  21          5

Could someone provide a SQL query or stored procedure to achieve this?

2

Hey, that’s an intersting question! Have you considered using a split function in SQL? You could create a custom table-valued function to split the string by commas. What are your thoughts on using functions versus inline SQL solutions for this task?

3

A practical method to manage this is using the STRING_SPLIT function available in SQL Server. You can split the string into individual records and insert them into your table. After splitting, loop through the results to insert each individual worker ID with the associated EditorId. Ensure your SQL Server version supports STRING_SPLIT, or you may need to write a user-defined function to achieve similar results. Handling strings in this way will maintain the data integrity and facilitate easy query operations.

4

You could also try using XML to achieve this! Convert your comma-separated string into XML, then use SQL Server’s XML features to shred that into rows. It’s a bit more complex but very reliable if done right. xml can parse strings quite effectively.

5

oh, this is fascinating! has anyone here tried using a cursors for inserting each item from a split string loop? i wonder how it performs compared to functions and XML methods. are there any performance trade-offs to consider with large datasets?