Storing the NewSequentialID() after Inserting using the Output Clause

A little while ago i wrote this post about returning a NewSequentialID() after inserting a row… Sometimes though you want to be able to do something with that ID within the stored procedure that you are in the scope of…

You can use the output clause to insert certain columns in the inserted table into a temporary table. For example:

The table we are working with…

CREATE TABLE dbo.junk (
      id uniqueidentifier NOT NULL
        DEFAULT newsequentialid()
    , name varchar(100)

CREATE TABLE dbo.junk_history (
      id uniqueidentifier NOT NULL
    , name varchar(100)
    , date datetime DEFAULT getutcdate()

The first table is the table we are inserting into, the second table we want to have filled with the newly inserted values including the id column…

The Stored procedure we have is as follows:

CREATE PROCEDURE dbo.junk_insert
    @name nvarchar(100)
    INSERT INTO dbo.junk (name)
        OUTPUT, INTO dbo.junk_history (id, name)
        SELECT @name

In the insert statement I’m getting the id and name from the row inserted and inserting that into the junk_history table… You can insert the data into a temporary table variable or an actual table.

DECLARE	@return_value int

EXEC	@return_value = [dbo].[junk_insert]
		@name = N'Jimbo Jumbalock'

SELECT	'Return Value' = @return_value


SELECT * FROM dbo.junk
SELECT * FROM dbo.junk_history

The above script calls the sproc and shows you the contents of both tables…

Note: that this was a very quickly put together script to get an example working, so please ignore any weirdness 🙂



Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: