使用表变量将多行插入到 SQL Server 表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14323163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting multiple rows into a SQL Server table using a table variable
提问by CM_Heroman
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.
我目前正在使用 SQL Server 2008，我正在尝试使用表变量创建一个语句，以将多行插入到表中。就目前而言，我必须在 4 个不同的位置（2 个选择语句、1 个插入和 1 个更新）插入要添加的信息，但希望能够创建单个表变量，因此我只需输入信息一次。任何帮助/建议将不胜感激。
This is an example of what I am trying to change.
这是我试图改变的一个例子。
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)
回答by Rozwel
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
回答by lante
To insert into table1values from table2:
插入table1值来自table2：
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
