locked
How to get the value of timestamp column after execution of Insert statement? RRS feed

  • Question

  • I want to retrieve the value of the timestamp column generated by SQL Server during the execution of Insert or Update statement.

    What is the best way to do that?

    I am using JDBC driver to access data. Would I get the value by calling Statement.getGeneratedKeys()? Is timestamp value treated similar to identity column value?

     

    Monday, July 28, 2008 8:43 PM

Answers

  • hi,

     dtonev wrote:

    I want to retrieve the value of the timestamp column generated by SQL Server during the execution of Insert or Update statement.

    What is the best way to do that?

    I am using JDBC driver to access data. Would I get the value by calling Statement.getGeneratedKeys()? Is timestamp value treated similar to identity column value?

     

     

    you can use a SELECT statement as soon as the insert/update operation has been performed, but you probably have a better approach using the SQL Server 2005 OUTPUT clause to return the required value(s) to your client app, like

     

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
     Id int IDENTITY,
     data varchar(5),
     ts timestamp NOT NULL
     );
    GO
    PRINT 'inserting..';
    INSERT INTO dbo.t (data)
     OUTPUT inserted.*
     VALUES ('d');
    PRINT 'updating..';
    UPDATE dbo.t
     SET data = 'b'
     OUTPUT inserted.*
     WHERE id = 1;
    PRINT 'deleting..';
    DELETE dbo.t
     OUTPUT deleted.*
     WHERE id = 1;
    GO
    PRINT 'Selecting after inserting..';
    INSERT INTO dbo.t (data)
     VALUES ('d2');
    DECLARE @curId int;
    SELECT @curId = SCOPE_IDENTITY()
    SELECT *--id, ts
     FROM dbo.t
     WHERE id = @curId;
    
    PRINT 'Selecting after updating...';
    UPDATE dbo.t 
     SET data = 'd3'
     WHERE id = @curId;
    SELECT *--id, ts
     FROM dbo.t
     WHERE id = @curId;
    GO
    DROP TABLE dbo.t;
    --<--------
    inserting..
    Id          data  ts
    ----------- ----- ------------------
    1           d     0x00000000000007E9
    updating..
    Id          data  ts
    ----------- ----- ------------------
    1           b     0x00000000000007EA
    deleting..
    Id          data  ts
    ----------- ----- ------------------
    1           b     0x00000000000007EA
    Selecting after inserting..
    Id          data  ts
    ----------- ----- ------------------
    2           d2    0x00000000000007EB
    Selecting after updating...
    Id          data  ts
    ----------- ----- ------------------
    2           d3    0x00000000000007EC
    

     

    as you can see the dml statement will output the required value(s) you can "capture" after you perform the dml in you client app..

    regards

    Tuesday, July 29, 2008 3:50 PM

All replies

  • hi,

     dtonev wrote:

    I want to retrieve the value of the timestamp column generated by SQL Server during the execution of Insert or Update statement.

    What is the best way to do that?

    I am using JDBC driver to access data. Would I get the value by calling Statement.getGeneratedKeys()? Is timestamp value treated similar to identity column value?

     

     

    you can use a SELECT statement as soon as the insert/update operation has been performed, but you probably have a better approach using the SQL Server 2005 OUTPUT clause to return the required value(s) to your client app, like

     

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t (
     Id int IDENTITY,
     data varchar(5),
     ts timestamp NOT NULL
     );
    GO
    PRINT 'inserting..';
    INSERT INTO dbo.t (data)
     OUTPUT inserted.*
     VALUES ('d');
    PRINT 'updating..';
    UPDATE dbo.t
     SET data = 'b'
     OUTPUT inserted.*
     WHERE id = 1;
    PRINT 'deleting..';
    DELETE dbo.t
     OUTPUT deleted.*
     WHERE id = 1;
    GO
    PRINT 'Selecting after inserting..';
    INSERT INTO dbo.t (data)
     VALUES ('d2');
    DECLARE @curId int;
    SELECT @curId = SCOPE_IDENTITY()
    SELECT *--id, ts
     FROM dbo.t
     WHERE id = @curId;
    
    PRINT 'Selecting after updating...';
    UPDATE dbo.t 
     SET data = 'd3'
     WHERE id = @curId;
    SELECT *--id, ts
     FROM dbo.t
     WHERE id = @curId;
    GO
    DROP TABLE dbo.t;
    --<--------
    inserting..
    Id          data  ts
    ----------- ----- ------------------
    1           d     0x00000000000007E9
    updating..
    Id          data  ts
    ----------- ----- ------------------
    1           b     0x00000000000007EA
    deleting..
    Id          data  ts
    ----------- ----- ------------------
    1           b     0x00000000000007EA
    Selecting after inserting..
    Id          data  ts
    ----------- ----- ------------------
    2           d2    0x00000000000007EB
    Selecting after updating...
    Id          data  ts
    ----------- ----- ------------------
    2           d3    0x00000000000007EC
    

     

    as you can see the dml statement will output the required value(s) you can "capture" after you perform the dml in you client app..

    regards

    Tuesday, July 29, 2008 3:50 PM
  • The information was very helpful. If found that Statement.getGeneratedKeys() method gives you only the value of the IDENTITY column. It does not return generated timestamp value. The OUTPUT clause works perfectly for both type of columns, but is limited to SQL Server 2005.

    Thanks again for the helpful tip.

     

    Dimitre Tonev

    http://shipka.com

     

    Friday, August 8, 2008 4:35 PM