Inserting multiple rows into a SQL Server table using a table variable
28,053
Solution 1
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
Solution 2
To insert into table1
values from table2
:
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
Author by
CM_Heroman
Updated on July 30, 2022Comments
-
CM_Heroman almost 2 years
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.
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)
-
marc_s over 11 yearsAnd of course, here
table2
would have to be a table variable and thus it should be@table2