使用表变量将多行插入到SQL Server表中
发布于 2021-05-10 21:16:33
我当前正在使用SQL Server
2008,并且正在尝试使用表变量创建语句以在表中插入多行。就目前而言,我必须将要添加的信息插入4个不同的位置(2个select语句,1个insert和1个update),但是希望能够创建一个表变量,因此我只需要输入信息一次。任何帮助/建议将不胜感激。
这是我试图改变的一个例子。
PRINT 'Before'
SELECT GROUPID, ModifiedBy, ModifiedDate
FROM TableXYZ
WHERE groupID in(ID1, ID2, ID3, ID4)
BEGIN TRAN
Insert into TableXYZ
(GROUPID)
VALUES
(ID1), (ID2), (ID3), (ID4)
UPDATE TableXYZ
SET existingdays = 15
,ModifiedBy = @userID
,ModifiedDate = @today
WHERE groupID in(ID1, ID2, ID3, ID4)
Set @RowCount = @@ROWCOUNT
PRINT 'After '
SELECT GROUPID, ModifiedBy, ModifiedDate
FROM TableXYZ
WHERE groupID in(ID1, ID2, ID3, ID4)
关注者
0
被浏览
66
1 个回答
-
这是您仅要输入一次信息所要寻找的东西吗?
DECLARE @IDList TABLE ( ID INT ) INSERT INTO @IDList ( ID ) VALUES (ID1) ,(ID2) ,(ID3) ,(ID4) PRINT 'Before' SELECT GROUPID, ModifiedBy, ModifiedDate FROM TableXYZ AS T INNER JOIN @IDList AS L ON T.GroupID = L.ID BEGIN TRAN Insert into TableXYZ (GROUPID) SELECT ID FROM @IDList UPDATE TableXYZ SET existingdays = 15 ,ModifiedBy = @userID ,ModifiedDate = @today FROM TableXYZ AS T INNER JOIN @IDList AS L ON T.GroupID = L.ID Set @RowCount = @@ROWCOUNT PRINT 'After ' SELECT GROUPID, ModifiedBy, ModifiedDate FROM TableXYZ AS T INNER JOIN @IDList AS L ON T.GroupID = L.ID