Inserting multiple rows into a SQL Server table using a table variable

Question

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)

It is not clear what you are trying to achieve - why would a single TVP be able to replace two selects, an insert and an update? — Oded
– Oded, Commented Jan 14, 2013 at 17:09
If you want to target a table with a variable then AFAIK, you will need to create a (prepared) statement inside your query using CONCAT() to concat your variable and all other query code. — inhan
– inhan, Commented Jan 14, 2013 at 17:16

Rozwel · Accepted Answer · 2013-01-14 17:29:08Z

16

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

answered Jan 14, 2013 at 17:29

Rozwel

2,0302 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lante · Accepted Answer · 2013-01-14 17:10:01Z

1

To insert into table1 values from table2:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

answered Jan 14, 2013 at 17:10

lante

7,3924 gold badges39 silver badges59 bronze badges

1 Comment

marc_s Over a year ago

And of course, here table2 would have to be a table variable and thus it should be @table2

Collectives™ on Stack Overflow

Inserting multiple rows into a SQL Server table using a table variable

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related