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

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?

This question is related to sql sql-server-2008 insert timestamp

The answer is


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.


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

create table demo ( id int, ts timestamp )

insert into demo(id,ts) values (1, DEFAULT)


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

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

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.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?

Examples related to insert

How to insert current datetime in postgresql insert query How to add element in Python to the end of list using list.insert? Python pandas insert list into a cell Field 'id' doesn't have a default value? Insert a row to pandas dataframe Insert at first position of a list in Python How can INSERT INTO a table 300 times within a loop in SQL? How to refresh or show immediately in datagridview after inserting? Insert node at a certain position in a linked list C++ select from one table, insert into another table oracle sql query

Examples related to timestamp

concat yesterdays date with a specific time How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Pandas: Convert Timestamp to datetime.date Spark DataFrame TimestampType - how to get Year, Month, Day values from field? What exactly does the T and Z mean in timestamp? What does this format means T00:00:00.000Z? Swift - iOS - Dates and times in different format Convert timestamp to string Timestamp with a millisecond precision: How to save them in MySQL