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)    

Tetherball

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? – Keynes

Can you explain your question more detailed? – Handshake

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. – Amatory

This is the existing code that I would like to change – Tetherball

Answer 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

Disapprove

Answer 2

To insert into table1 values from table2:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

Jacobin

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