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)
)
GO

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

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)
AS
BEGIN
    INSERT INTO dbo.junk (name)
        OUTPUT inserted.id, inserted.name INTO dbo.junk_history (id, name)
        SELECT @name
END
GO

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

GO

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 🙂

References:

• http://msdn.microsoft.com/en-us/library/ms177564.aspx