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?

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.

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.

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

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.