Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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)
share|improve this question
2  
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♦ Jan 14 at 17:09
Can you explain your question more detailed? – Hamlet Hakobyan Jan 14 at 17:16
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 Jan 14 at 17:16
This is the existing code that I would like to change – user1860451 Jan 14 at 17:17

2 Answers

up vote 0 down vote accepted

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
share|improve this answer
up vote 0 down vote

To insert into table1 values from table2:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2
share|improve this answer
1  
And of course, here table2 would have to be a table variable and thus it should be @table2 – marc_s Jan 14 at 17:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.