问题描述
我当前正在使用SQL Server 2008,并且正在尝试使用表变量创建语句以在表中插入多行.就目前情况而言,我必须将添加的信息插入4个不同的位置(2个select语句,1个insert和1个update),但是希望能够创建一个表变量,因此我只需要输入信息一次.任何帮助/建议将不胜感激.
I am currently using SQL Server 2008, and I am trying to create a statement using a table variable to insert multiple rows into the table. As it stands right now, I have to insert the information being added in 4 different spots(2 select statements, 1 insert and 1 update), but would like to be able to create a single table variable, so I only have to enter the information once. Any help/suggestions would be greatly appreciated.
这是我要更改的示例.
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)
推荐答案
这是您仅输入一次信息所要寻找的东西吗?
Is this what you are looking for in terms of only entering the information once?
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
这篇关于使用表变量将多行插入到SQL Server表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!