store SQL variable from INSERT query FROM clause result column

Question

This is probably trivial to most of you, but I haven't been writing stored procedures for very long (6 months only). I'd like to be able to set the variable @testid based on one of the columns being used for an INSERT query. How can I do this?

DECLARE @testid INT;

INSERT INTO [exporttestresultreport] (
    [testid],
    [othercolumn]
) 
SELECT
    [testid],  -- <======= how can I set my variable based on this column?
    [othercolumn]
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid

gbn · Accepted Answer · 2011-08-03 05:21:05Z

3

DECLARE @testid INT;

DECLARE @test TABLE (testid int);

INSERT INTO [exporttestresultreport] (
    [testid],
    [othercolumn]
) 
OUTPUT INSERTED.testID INTO @test
SELECT
    [testid],  -- <======= how can I set my variable based on this column?
    [othercolumn]
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid;

SELECT @testid = testid FROM @test;

An INSERT..SELECT.. is inherently multirow so it doesn't make semse to allow assigning a value to a scalar variable: what row should be used for the value?

edited Aug 3, 2011 at 5:21

answered Aug 2, 2011 at 18:52

gbn

434k84 gold badges602 silver badges690 bronze badges

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

1 Comment

JustBeingHelpful Over a year ago

If you correct your syntax I will mark it as the answer. That's why I chose the other fella's. How can I combine those queries?

Aaron Bertrand · Accepted Answer · 2011-08-02 18:56:55Z

1

DECLARE @testid INT;

DECLARE @t TABLE(t INT);

INSERT exporttestresultreport
(
    testid, othercolumn
)
OUTPUT INSERTED.testid INTO @t
SELECT testid, othercolumn 
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid;

SELECT @testid = t FROM @t;

-- not sure what you want to do if there are multiple rows in the insert

answered Aug 2, 2011 at 18:56

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

2 Comments

JustBeingHelpful Over a year ago

Do you know how I can add multiple temp tables in the same OUTPUT line?

Aaron Bertrand Over a year ago

Sure, you can add more columns to @t and then add , INSERTED.othercolumn to the OUTPUT clause. But if your intention is to output ALL of the columns into variables, then why not just

SELECT @testid = testid, @othercolumn = othercolumn FROM ...; INSERT exporttestresultreport SELECT @testid, @othercolumn;

?

Collectives™ on Stack Overflow

store SQL variable from INSERT query FROM clause result column

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related