asked
OUTPUT Identity field when doing a INSERT
Hi all I need to OUTPUT the identity field after doing a INSERT on a table. I need that value to be inserted into another table. I have the following table (TrxId being the identity column) Am trying to do a INSERT and capture the TrxId into a #table. INSERT Trx ([AccId] ,[DevId] ,[ShfId] ,[DevUsrId] ,[FullAmount] ,[BillAmount] OUTPUT INSERTED.TrxId INTO #NewTrx) Am i missing something? Thanks
If the INSERT operation will only ever output a single value, you can use [SCOPE_IDENTITY()][1] to retrieve the identity value for an insert within the scope of the current execution (your query). But, this won't work well if your insert puts in more than one row. For that I suggest you read up on how to use the [OUTPUT clause][2]. That will allow you to capture multiple values. [1]:
http://msdn.microsoft.com/en-us/library/ms190315.aspx [2]:
http://msdn.microsoft.com/en-US/library/ms177564(v=SQL.90).aspx
13 People are following this question.
