如何从变量进行批量插入？
Grades我的数据库中有一个表：
PersonId Marks
---------------------
1 10
2 15
3 13
4 11
我有这个变量
PersonID varchar = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20"
Marks varchar = "0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0"
我想使用批量插入将此值插入到等级表中，如果不同，我想比较表和 var 之间的标记，我将用新标记替换它
首先，您需要使用XMLMethod拆分数据，然后您可以插入数据Table Variable，您可以通过该数据将Grade表更新为可用的新标记Table Variable：-
DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id NVARCHAR(300),
marks NVARCHAR(300)
);
;WITH CTE
AS (
SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
ROW_NUMBER() OVER(ORDER BY
(
SELECT NULL
)) RN
FROM
(
SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)),
CTE1
AS (
SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
ROW_NUMBER() OVER(ORDER BY
(
SELECT NULL
)) RN
FROM
(
SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a))
INSERT INTO @StudentsMark
SELECT C.id,
C1.marks
FROM CTE C
LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
UPDATE G
SET
G.Marks = M.marks
FROM grades G
INNER JOIN @StudentsMark M ON G.PersonId = M.id;
SELECT *
FROM grades;
结果 ：
PersonId Marks
1 0
2 1
3 2
4 5
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句