how to insert values of a column of a table dynamically

Question

How to pass an array to SQL SERVER 2008 stored procedure and insert all the values of a array into a database table.

For Example: From ASP.NET mvc C#, I would pass a parameter having multiple values separated by comma

as below

string category = "Cat1, Cat2, Cat3, Cat4";

I would like to insert all the values of above string in a table called Categories.

How could I achieve this using SQL SERVER 2008.

Questions seeking debugging help ("**why isn't this code working?**") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it **in the question itself**. Questions without a **clear problem statement** are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — Rick S, Feb 27 '15 at 17:54
http://stackoverflow.com/questions/14811316/separate-comma-separated-values-and-store-in-table-in-sql-server — Rick S, Feb 27 '15 at 18:43

score 0 · Accepted Answer · edited Jan 18 '21 at 12:32

You can do it easily by converting CSV values to XML format and then use CROSS APPLY on it to split it to rows and insert it your table.

CREATE TABLE #Categories(VALUE VARCHAR(100))

DECLARE @STR VARCHAR(500)='Cat1, Cat2, Cat3, Cat4'

INSERT INTO #Categories(VALUE)
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'VALUE' 
FROM  
(
     -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
     SELECT CAST ('<M>' + REPLACE(@STR, ',', '</M><M>') + '</M>' AS XML) AS Data             
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

SELECT * FROM #Categories

Click here to view result

how to insert values of a column of a table dynamically

1 Answers1