In previous content I showed how to use the OUTPUT command for printing inserted or updated records in a T-SQL query. It's simple, it's powerfull and saves us lines of code. Usually we need use these values in some larger script and assigned them to variables or insert them into other tables. Let's see how to do it.

Usually when you are working with large database with many queries per second, you use transaction to make sure that you are working with the last inserted value. This is not optimal solution because you lock tables and larger scripts can take long time to complete. You can do it easily in MSSQL 2005 with the OUTPUT command without using transaction.

First I will create an example table where we will insert some data:

-- I create some sample table where I will insert data
CREATE TABLE [dbo].[outputTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Test] [varchar](50) NUL
) ON [PRIMARY

In order to save returned values from OUTPUT command, you can specify table or values are print the values in Query Analyzer. You can't use something like OUTPUT @id = inserted.id, but you can use table variable.

-- I declare table variable, you can add more fields if you need
DECLARE @MyTableVar table(ID int, Test varchar(50));

Here is sample Insert. Update will be similar with same OUTPUT part.

-- Sample insert with Output to table variable
INSERT INTO dbo.outputTest (Test)
OUTPUT inserted.ID, inserted.Test INTO @MyTableVar
VALUES ('Test')

If we need use one single variable

-- Varaible to use, but you can work directly with table variable in your script
Declare @id int
-- Assign value to @i from table, where we put Output data before
SELECT @id = ID FROM @MyTableVar

Now you can use @id variable as you need. Of course easier is use directly SELECT @id FROM @MyTableVar if it's possible.

Here is whole example with additional insert and print commands so that you can try it in Query Analyzer and play with it to better understand how it works.

-- I create some sample table where I will insert data
CREATE TABLE [dbo].[outputTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Test] [varchar](50) NULL
) ON [PRIMARY]

-- I declare table variable, you can add more fields if you need
DECLARE @MyTableVar table(ID int, test varchar(50));

-- Varaible to use, but you can work directly with table variable in your script
Declare @id int

-- Sample insert with Output to table variable
INSERT INTO dbo.outputTest (Test)
OUTPUT inserted.ID, inserted.Test INTO @MyTableVar
VALUES ('Test')

-- Assign value to @i from table, where we put Output data before
SELECT @id = ID FROM @MyTableVar

-- example print to see what happend
Print @id

-- Insert again, to test field variable from previous step
INSERT INTO dbo.outputTest (Test)
OUTPUT inserted.ID, inserted.Test INTO @MyTableVar
VALUES (@id)

-- again Assign value to @i from table, where we put Output data before
SELECT @id = ID from @MyTableVar

-- again example print
Print @id

-- see what we have in table
SELECT * FROM dbo.outputTest

Here is what you get as result of this example

(1 row(s) affected)
1

(1 row(s) affected)
2
ID          Test
----------- --------------------------------------------------
1           Test
2           1

(2 row(s) affected

Tweet