locked
How to explicitly set the value of a timestamp(rowversion) column? RRS feed

  • Question

  • i need to import table tbl1 from DB1 into the same table structure called also tbl1 but in DB2.

    So, if table CREATE TABLE tbl1 (myKey int PRIMARY KEY, myValue int, RV rowversion);

    so how can i make this statment work:

           insert into DB2.tbl1(myKey, myValue,  RV )

            Select myKey, myValue,  RV from DB1.tbl1

     

    so if i compare a row from both tables it will have all its columns values equal.

     

    note:

    what i want is something like this article:

            How to Insert Values into an Identity Column in SQL Server
               http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

    Saturday, November 27, 2010 8:44 PM

Answers

  • The only way you can do this is by converting the rowversion column to a different type, to prevent SQL Server from trying to manage it automatically.  There is no corollary to IDENTITY_INSERT for rowversion type columns.

     

    • Edited by masher2 Saturday, November 27, 2010 9:38 PM
    • Proposed as answer by Naomi N Sunday, November 28, 2010 1:41 AM
    • Marked as answer by KJian_ Friday, December 3, 2010 7:10 AM
    Saturday, November 27, 2010 9:17 PM
  • It can't be done.  By definition in a rowversion column, every time you update or insert a row with a rowversion column, that rowversion is set to a unique value (that is unique within the particular database) that will never be repeated in that database.  So, you are not allowed to specify the row version value.

    Tom

    • Proposed as answer by Naomi N Sunday, November 28, 2010 1:41 AM
    • Marked as answer by KJian_ Friday, December 3, 2010 7:10 AM
    Saturday, November 27, 2010 9:33 PM
  • Any progress?

    Here is a related article on multi-user concurrency control: http://www.sqlusa.com/articles2005/rowversion/

    Demo follows:

    CREATE TABLE Alpha (
    	AlphaID int identity(1,1) PRIMARY KEY,
    	Col1 nvarchar(32) default (''),
    	colRowVersion rowversion,
    	ModifiedDate date default(CURRENT_TIMESTAMP));
    GO
    
    INSERT Alpha DEFAULT VALUES;
    GO 1000
    
    SELECT * FROM Alpha ORDER BY AlphaID
    /* 
    AlphaID	Col1	colRowVersion	ModifiedDate
    ....
    995		0x0000000000000BE5	2010-12-02
    996		0x0000000000000BE6	2010-12-02
    997		0x0000000000000BE7	2010-12-02
    998		0x0000000000000BE8	2010-12-02
    999		0x0000000000000BE9	2010-12-02
    1000		0x0000000000000BEA	2010-12-02
    */
    
    INSERT Alpha(colRowVersion) VALUES (0x0000000000000BEB )
    /*
    Msg 273, Level 16, State 1, Line 1
    Cannot insert an explicit value into a timestamp column. 
    Use INSERT with a column list to exclude the timestamp column, 
    or insert a DEFAULT into the timestamp column.
    
    */
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL Server 2008 Training
    • Marked as answer by Reader Man Me Tuesday, December 7, 2010 5:41 PM
    Thursday, December 2, 2010 10:54 AM

All replies

  • The only way you can do this is by converting the rowversion column to a different type, to prevent SQL Server from trying to manage it automatically.  There is no corollary to IDENTITY_INSERT for rowversion type columns.

     

    • Edited by masher2 Saturday, November 27, 2010 9:38 PM
    • Proposed as answer by Naomi N Sunday, November 28, 2010 1:41 AM
    • Marked as answer by KJian_ Friday, December 3, 2010 7:10 AM
    Saturday, November 27, 2010 9:17 PM
  • It can't be done.  By definition in a rowversion column, every time you update or insert a row with a rowversion column, that rowversion is set to a unique value (that is unique within the particular database) that will never be repeated in that database.  So, you are not allowed to specify the row version value.

    Tom

    • Proposed as answer by Naomi N Sunday, November 28, 2010 1:41 AM
    • Marked as answer by KJian_ Friday, December 3, 2010 7:10 AM
    Saturday, November 27, 2010 9:33 PM
  • Any progress?

    Here is a related article on multi-user concurrency control: http://www.sqlusa.com/articles2005/rowversion/

    Demo follows:

    CREATE TABLE Alpha (
    	AlphaID int identity(1,1) PRIMARY KEY,
    	Col1 nvarchar(32) default (''),
    	colRowVersion rowversion,
    	ModifiedDate date default(CURRENT_TIMESTAMP));
    GO
    
    INSERT Alpha DEFAULT VALUES;
    GO 1000
    
    SELECT * FROM Alpha ORDER BY AlphaID
    /* 
    AlphaID	Col1	colRowVersion	ModifiedDate
    ....
    995		0x0000000000000BE5	2010-12-02
    996		0x0000000000000BE6	2010-12-02
    997		0x0000000000000BE7	2010-12-02
    998		0x0000000000000BE8	2010-12-02
    999		0x0000000000000BE9	2010-12-02
    1000		0x0000000000000BEA	2010-12-02
    */
    
    INSERT Alpha(colRowVersion) VALUES (0x0000000000000BEB )
    /*
    Msg 273, Level 16, State 1, Line 1
    Cannot insert an explicit value into a timestamp column. 
    Use INSERT with a column list to exclude the timestamp column, 
    or insert a DEFAULT into the timestamp column.
    
    */
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL Server 2008 Training
    • Marked as answer by Reader Man Me Tuesday, December 7, 2010 5:41 PM
    Thursday, December 2, 2010 10:54 AM
  • Thanks very much to all of u guys ( masher2, Tom Cooper, SQLUSA ).
    Tuesday, December 7, 2010 5:43 PM