SQL Server 2005: T-SQL INSERT INTO and OUTPUT timestamp to a variable

Question

Example:

IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1;
GO

CREATE TABLE T1 (id int PRIMARY KEY, timestamp);
GO

INSERT INTO T1(id) VALUES (1);
GO

declare @v timestamp;
INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10);
select @v

---

How can I get the inserted.timestamp into variable @v?

Answer 1

One way would be to use identity:

declare @v timestamp; 
declare @ID int; 
INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10); 
select @ID =@@IDENTITY;
select @v=timestamp from T1 where id=@ID;

(edit)... of course that would require an identity column. Since you know the value of the ID you are inserting then you don't even need to do this, just select the same row after you insert it using your original value.

Is the point you would like to do this all in a single statement? You could probably do it with a trigger but seems unnecessary.

Answer 2

Well, you're inserting a specific value into the ID column, which is the primary key - so just read out the row once you've inserted it...

INSERT INTO T1(id) VALUES (1);
GO

DECLARE @v timestamp

SELECT @v = TIMESTAMP 
FROM dbo.T1 
WHERE ID = 1

SELECT @v

Answer 3

This is what I got to work:

IF OBJECT_ID('T1') IS NOT NULL 
DROP TABLE T1; 
GO 

CREATE TABLE T1 (id int PRIMARY KEY, timestamp); 
GO 

INSERT INTO T1(id) VALUES (1); 
GO 

declare @v as table ([timestamp] varbinary) --timestamp; 
INSERT INTO T1(id) 
OUTPUT inserted.[timestamp] into @v 
VALUES (10); 

select * from @v 

One thing you need to realize is a timestamp field cannot be manually populated. So you must use some other type in your output table. ANd BTW timestamp is deprecated, I would not use it in new development at all. Use rowversion instead. And timestamp doesn't mean it will be a date for those who think it should be like the ANSII Standard, IN SQL Server it is not a date or convertable to a date.