使用表变量将多行插入到SQL Server表中 [英] Inserting multiple rows into a SQL Server table using a table variable

问题描述

我当前正在使用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表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！