SQL Server Cannot insert an explicit value into a timestamp column

Question

When using this statement  create table demo       ts timestamp    insert into demo select current timestamp   I get the following error      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   How do I insert the current time to a timestamp column

User · Answer

There is some good information in these answers. Suppose you are dealing with databases which you can't alter, and that you are copying data from one version of the table to another, or from the same table in one database to another. Suppose also that there are lots of columns, and you either need data from all the columns, or the columns which you don't need don't have default values. You need to write a query with all the column names.

Here is a query which returns all the non-timestamp column names for a table, which you can cut and paste into your insert query. FYI: 189 is the type ID for timestamp.

declare @TableName nvarchar(50) = 'Product';

select stuff(
    (select 
        ', ' + columns.name
    from 
        (select id from sysobjects where xtype = 'U' and name = @TableName) tables
        inner join syscolumns columns on tables.id = columns.id
    where columns.xtype <> 189
    for xml path('')), 1, 2, '')

Just change the name of the table at the top from 'Product' to your table name. The query will return a list of column names:

ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate, rowguid, ModifiedDate

If you are copying data from one database (DB1) to another database(DB2) you could use this query.

insert DB2.dbo.Product (ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate, rowguid, ModifiedDate)
select ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate, rowguid, ModifiedDate 
from DB1.dbo.Product

User · Answer

You can t insert the values into timestamp column explicitly  It is auto-generated  Do not use this column in your insert statement  Refer http   msdn microsoft com en-us library ms182776 SQL 90  aspx for more details   You could use a datetime instead of a timestamp like this   create table demo       ts datetime    insert into demo select current timestamp  select ts from demo   Returns   2014-04-04 09 20 01 153

User · Answer

According to MSDN  timestamp     Is a data type that exposes automatically generated  unique binary   numbers within a database  timestamp is generally used as a mechanism   for version-stamping table rows  The storage size is 8 bytes  The   timestamp data type is just an incrementing number and does not   preserve a date or a time  To record a date or time  use a datetime   data type    You re probably looking for the datetime data type instead

User · Answer

If you have a need to copy the exact same timestamp data  change the data type in the destination table from timestamp to binary 8  -- i used varbinary 8  and it worked fine   This obviously breaks any timestamp functionality in the destination table  so make sure you re ok with that first

User · Answer

Assume Table1 and Table2 have three columns A  B and TimeStamp  I want to insert from Table1 into Table2   This fails with the timestamp error   Insert Into Table2 Select Table1 A  Table1 B  Table1 TimeStamp From Table1   This works   Insert Into Table2 Select Table1 A  Table1 B  null From Table1

User · Answer

How to insert current time into a timestamp with SQL Server  In newer versions of SQL Server  timestamp is renamed to RowVersion   Rightly so  because timestamp name is misleading  SQL Server s timestamp IS NOT set by the user and does not represent a date or a time   Timestamp is only good for making sure a row hasn t changed since it s been read  If you want to store a date or a time  do not use timestamp  you must use one of the other datatypes  like for example datetime  smalldatetime  date  time or DATETIME2 For example  create table wtf       id INT      leet timestamp    insert into wtf  id  values  15   select   from wtf  15    0x00000000000007D3    timestamp  in mssql is some kind of internal datatype   Casting that number to datetime produces a nonsense number

User · Answer

create table demo       id int      ts timestamp    insert into demo id ts    values  1  DEFAULT

[sql] SQL Server: Cannot insert an explicit value into a timestamp column

Examples related to sql

Examples related to sql-server-2008

Examples related to insert

Examples related to timestamp