您可以一次性完成这一切,而不是使用WHILE
环形。因此,在这段代码中,它将把值推入临时表的行中,然后再使用它INSERT
进入目标表:
将逗号分隔值拆分为行的示例代码取自:
将逗号分隔的列值转换为行 https://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows
DECLARE @string AS NVARCHAR(MAX);
DECLARE @substring AS NVARCHAR(50);
SET @string = 'Apple, Banana, Cherry, Orange, Mango'
SELECT Split.a.value('.', 'VARCHAR(100)') AS Fruits
INTO #fruits
FROM
(
SELECT CAST ('<M>' + REPLACE(@string, ', ', '</M><M>') + '</M>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/M') AS Split ( a );
-- show what's in the temp table
SELECT *
FROM #fruits
此时,临时表中的行中已包含值,您可以使用它来填充目标表,如下所示:
INSERT INTO tbFruit ( Name )
SELECT Fruits FROM #fruits
-- show what's in the target table
SELECT * FROM #target_table
-- tidy up
DROP TABLE #fruits
SQL 小提琴演示 http://sqlfiddle.com/#!6/d41d8/22738
TSQL 代码:
DECLARE @string AS NVARCHAR(MAX) = 'Apple, Banana, Cherry, Orange, Mango'
DECLARE @substring AS NVARCHAR(50)
SELECT Split.a.value('.', 'VARCHAR(100)') AS Fruits
INTO #fruits
FROM ( SELECT CAST ('<M>' + REPLACE(@string, ', ', '</M><M>') + '</M>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/M') AS Split ( a )
CREATE TABLE #target_table ( Fruits NVARCHAR(50) )
INSERT INTO #target_table
( fruits )
SELECT *
FROM #fruits
SELECT * FROM #target_table
DROP TABLE #fruits
DROP TABLE #target_table
Results:
| FRUITS |
|--------|
| Apple |
| Banana |
| Cherry |
| Orange |
| Mango |