locked
INSERT...SELECT command with a timestamp column RRS feed

  • Question

  • Hi,

    I am trying to do an insert select command with a timestamp column. I ran the code :

    INSERT into Table1 SELECT * from Table2 where id=10

     

    the error below appears:

    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.

     

    I cannot input the values one by one bec it has 1000 rows.

     

    How can I include the DEFAULT into the select statement?

     

    please help.thanks.

     

     

    • Moved by Tom Phillips Wednesday, 18 August 2010 8:18 PM TSQL Question (From:SQL Server Database Engine)
    Tuesday, 21 October 2008 11:06 AM

Answers

  • As I showed in my example, you explicitly specify the columns you want to insert into, so list them all except for the timestamp column.

    By excluding the timestamp column you won't be directly inserting into it and sql server will automatically generate the value.

     

    Wednesday, 22 October 2008 5:29 AM

All replies

  • You can't insert explicit values into a timestamp column.

    Timestamp values are unique binary numbers that are automatically generated.

    You will need to explicitly state the columns you are inserting data into:

     

    insert into <table>  (<col 1>, <col 2>...) values (x, y....) 

     

    or give the timestamp col a default value.

     

     

    Tuesday, 21 October 2008 12:04 PM
  • how do I give the timestamp col a default value? (I hope i do not alter the table)

     

    what do you suggest I use to migrate a 1000 row data into a table with existing data also?

     

    Please help...thanks.

    Wednesday, 22 October 2008 1:19 AM
  • you do not need to specify a value for the timestamp column it is automatically populated - see http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx

    is the data in the 1000 rows table in the same database? what version of sql are you using 2008? 2005?
    Wednesday, 22 October 2008 1:55 AM
  • Nope...i want to migrate 1000 rows from one DB to another. I am using SQL server 2005.

     

    I am running the insert:

     

    use [DB2]

    insert into Table2 select * from DB1.dbo.Table1 where id=9

     

    I get the error :

    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.

     

    DB1.dbo.Table1 has a column with a timestamp as the datatype. How can I migrate the 1000 rows? Or How do I solve the error above? I f I exclude the timestamp...it errors and states:

     

    Msg 213, Level 16, State 1, Line 1

    Insert Error: Column name or number of supplied values does not match table definition.

     

    Do I have to exclude the timestamp? if yes, how do i do it without writing the 1000 rows in the values?  

     

    Please help.thanks

     

     

    Wednesday, 22 October 2008 2:53 AM
  • As I showed in my example, you explicitly specify the columns you want to insert into, so list them all except for the timestamp column.

    By excluding the timestamp column you won't be directly inserting into it and sql server will automatically generate the value.

     

    Wednesday, 22 October 2008 5:29 AM
  • thanks for the reply. i already did what you said.

     

    Monday, 27 October 2008 1:33 AM
  • I am also hving the same issue. Is there an easier way to leave out the timestamp field instead of list all of the other fields except timestamp? I have a boat load of fields and would like to not have to list them all. Thanks
    Thursday, 22 January 2009 8:28 PM
  • If the issue is saving on typing, you can do:

    1. Grab the Columns node (folder) of the appropriate table in Object Explorer, drag it to the INSERT statement and drop it into "()". Then remove columns not needed.

    2. Script out table as INSERT and modify script.

    Let us know if helpful.


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Edited by Kalman Toth Thursday, 9 November 2017 12:38 AM
    Friday, 23 January 2009 10:02 AM
  • The question hasn't been answered - what if you're using s/w that won't allow you to omit the timestamp colum from the list of columns to update.  How do you insert a DEFAULT value into a column of datatype TIMESTAMP?


    Wednesday, 21 October 2009 1:53 PM
  • Okay here is the hard way.

    1. Make sure your ts field is the last column in the table and that all the columns match between the old and new table

    2. Use a script like this...

    select * into #tmpTable from myFromTableName

    Alter table #tmpTable drop column ts

    insert into myToTableName select *, null from #tmpTable

     

    That should get all of your columns without having to specify the names.  I will frequently use that when I need to copy data back to the same table as well I just add where clauses and update the temp table's primary key values as needed before reinserting it.

    Hope that helps,

    Nathan

    • Proposed as answer by Dvd718 Thursday, 11 August 2016 8:16 PM
    Wednesday, 18 August 2010 5:03 PM
  • Select all of the columns from the table you are grabbing data from, except for your time stamp, use NULL instead:

    So like this

    Insert TableToBeUpdated

    Select Field1, Field2, NULL from TableGettingDataFrom

    Sql will throw the tome stand value in there for you

     

    Friday, 23 September 2011 3:04 PM
  • There are some challenges when copying identity and rowversion (formerly timestamp ) columns.
    Mapping:
    INT IDENTITY  --> INT
    ROWVERSION --> BIGINT
    See demo:
    USE tempdb;
    GO
    CREATE TABLE Celebrity ( 
      CelebrityID INT    IDENTITY    PRIMARY KEY, 
      FirstName   VARCHAR(25), 
      LastName    VARCHAR(30), 
      NickName    VARCHAR(30),
      VERSIONSTAMP  ROWVERSION) 
    GO 
    
    INSERT Celebrity (FirstName, LastName, NickName)
    VALUES 
    ('Jessica', 'Simpson', 'jinx'),  -- Nickname by former President Bush
    ('Frank','Sinatra', 'Chairman of Board'),
    ('Doris','Day', 'Do-Do'),
    ('Elvis', 'Presley', 'King of Rock')
    GO
    
    CREATE TABLE CelebrityCopy ( 
      CelebrityID INT      PRIMARY KEY, 
      FirstName   VARCHAR(25), 
      LastName    VARCHAR(30), 
      NickName    VARCHAR(30),
      bVERSIONSTAMP  bigint) 
    GO 
    
    INSERT CelebrityCopy SELECT * FROM Celebrity
    GO
    
    SELECT * FROM CelebrityCopy
    GO
    
    /* 
    CelebrityID	FirstName	LastName	NickName	bVERSIONSTAMP
    1	Jessica	Simpson	jinx	141701
    2	Frank	Sinatra	Chairman of Board	141702
    3	Doris	Day	Do-Do	141703
    4	Elvis	Presley	King of Rock	141704
    */
    
    
    

     Related article: http://www.sqlusa.com/articles2005/rowversion/


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    • Edited by Kalman Toth Thursday, 9 November 2017 12:39 AM
    Friday, 23 September 2011 3:21 PM
  • WOW WHY DON'T YOU JUST COPY WHAT THE ERROR SAID NEXT TIME ROFL

    steven frierdich

    Monday, 1 July 2019 10:35 AM
  • Not one person rofl who replied answered the original question which was

    How do you insert a DEFAULT value into a column of datatype TIMESTAMP?


    steven frierdich

    Monday, 1 July 2019 10:01 PM