使用表变量将多行插入到SQL Server表中

Question

我当前正在使用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)

Answer 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