8

The OUTPUT clause is compatible with SQL Server 2005 but not SQL Server 2000.

How do I convert this command to work in SQL Server 2000?

CREATE TABLE sample
(
 ID uniqueidentifier NOT NULL DEFAULT newid(),
 Title varchar(30) NOT NULL
)

INSERT INTO sample (Title)
OUTPUT INSERTED.ID
VALUES ('Test1')

I need the command to retrieve the ID since the INSERT command needs to be called from a stored procedure.

Thanks for any help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shay
  • 1,680
  • 2
  • 26
  • 39

1 Answers1

14
DECLARE @uid uniqueidentifier 
SET @uid  = newid()

INSERT INTO sample (ID, Title)
VALUES (@uid,'Test1')

SELECT @uid AS ID
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    This is a generic `INSERT` command that works on different tables (some with `int IDENTITY(1,1)` and some with `uniqueidentifier`).I need to use the `DEFAULT newid()`. – Shay May 02 '11 at 23:01
  • @ShayN - This is the only way of doing it in SQL Server 2000. There is no equivalent of `OUTPUT` or `SCOPE_IDENTITY()` for `uniqueidentifier` columns. Although I suppose you could simulate the `OUTPUT` clause by adding an `INSERT` trigger to the table that does `SELECT inserted.id` but that would apply to all `insert`s on the table. – Martin Smith May 02 '11 at 23:02
  • I already saw the trigger solution on other website and I wouldn't be able to use it. I needed to confirm that there is no equivalent. Thank you! – Shay May 02 '11 at 23:12