How to pass an array to SQL SERVER 2008 stored procedure and insert all the values of a array into a database table.
如何将数组传递给SQL SERVER 2008存储过程并将数组的所有值插入数据库表。
For Example: From ASP.NET mvc C#, I would pass a parameter having multiple values separated by comma
例如：从ASP.NET mvc C＃，我将传递一个参数，该参数具有由逗号分隔的多个值
as below
如下
string category = "Cat1, Cat2, Cat3, Cat4";
I would like to insert all the values of above string in a table called Categories.
我想在名为Categories的表中插入上面字符串的所有值。
How could I achieve this using SQL SERVER 2008.
我怎样才能使用SQL SERVER 2008实现这一目标。
1 个解决方案
#1
0
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.
您可以通过将CSV值转换为XML格式然后使用CROSS APPLY将其拆分为行并将其插入表格来轻松完成。
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
- 点击这里查看结果
#1
0
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.
您可以通过将CSV值转换为XML格式然后使用CROSS APPLY将其拆分为行并将其插入表格来轻松完成。
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
- 点击这里查看结果