Menu
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)
968
Simply define the correct columns in the select list: INSERT INTO dbo. tblEmpDetails (EmpId, StateId, CountryId, Comments) SELECT [<ReturnedColumn>], @StateId, @CountryId, @Comments from dbo. FN_ListToTable (',', @EmpIds);
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.
A table can store upto 1000 rows in one insert statement. If a user want to insert multiple rows at a time, the following syntax has to written. If a user wants to insert more than 1000 rows, multiple insert statements, bulk insert or derived table must be used.
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
To insert into table1 values from table2:
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
