Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OUTPUT clause Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 2:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:12 PM
Points: 2,132, Visits: 3,399
Hi folks . . . hopefully this is just a quick question.

Was just reading BOL regarding the OUTPUT clause. I'd like to take a column value generated from an INSERT and pass it to a variable.

Does the variable have to be a TABLE variable, or is there a way to pass it into a common variable such as INT or NVARCHAR (or, in my particular situation, a UNIQUEIDENTIFIER)?

Thanks!
Post #1008837
Posted Thursday, October 21, 2010 3:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 6,544, Visits: 8,758
You can either use a table (table variable, temporary table, permanent table) (results to into it), or not (results to client). But not to a "regular" variable. See the BOL OUTPUT Clause entry.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1008858
Posted Thursday, October 21, 2010 3:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:12 PM
Points: 2,132, Visits: 3,399
Okay, that's what I figured.

I ended up just creating a simple SELECT statement for the column immediately following the INSERT. I'm just writing a quick 'n dirty, and it's insignificant enough that having to create a TABLE variable for it would be unnecessary overkill.

Thanks!
Post #1008861
Posted Thursday, October 21, 2010 3:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 6,544, Visits: 8,758
Ray K (10/21/2010)
Okay, that's what I figured.

I ended up just creating a simple SELECT statement for the column immediately following the INSERT. I'm just writing a quick 'n dirty, and it's insignificant enough that having to create a TABLE variable for it would be unnecessary overkill.

Thanks!


Say what? Overkill???

DECLARE @test TABLE (Col1 uniqueidentifier);

INSERT INTO .....
OUTPUT i.MyUniqueIdentifierColumn INTO @test
....

SELECT * FROM @test;



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1008876
Posted Thursday, October 21, 2010 4:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:12 PM
Points: 2,132, Visits: 3,399
WayneS (10/21/2010)
Say what? Overkill???

Edit: my original reply was a bit too flippant -- didn't want anyone to take it the wrong way!

Maybe "overkill" wasn't the right word -- rather, what I was doing wasn't terribly big, and I didn't think it necessitated the need to create a table variable.
Post #1008881
Posted Friday, October 22, 2010 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 2:10 AM
Points: 138, Visits: 236
see Examples at below link:

http://mssqlsolutions.blogspot.com/2008/08/how-to-use-output-clause.html



Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
Post #1009228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse