SQL Server：无法在时间戳列中插入显式值

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

提问by juergen d

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

无法在时间戳列中插入显式值。将 INSERT 与列列表一起使用以排除时间戳列，或将 DEFAULT 插入时间戳列

How do I insert the current time to a timestampcolumn?

如何将当前时间插入到timestamp列中？

回答by Andomar

According to MSDN, timestamp

根据 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.

是一种在数据库中公开自动生成的唯一二进制数的数据类型。时间戳通常用作版本标记表行的机制。存储大小为 8 个字节。时间戳数据类型只是一个递增的数字，不保留日期或时间。要记录日期或时间，请使用日期时间数据类型。

You're probably looking for the datetimedata type instead.

您可能正在寻找datetime数据类型。

回答by CINCHAPPS

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.

如果您需要复制完全相同的时间戳数据，请将目标表中的数据类型从时间戳更改为 binary(8)——我使用了 varbinary(8) 并且它工作正常。

This obviously breaks any timestamp functionality in the destination table, so make sure you're ok with that first.

这显然会破坏目标表中的任何时间戳功能，因此请先确保您对此感到满意。

回答by Vinay Kumar Chella

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).aspxfor more details.

您不能显式地将值插入时间戳列。它是自动生成的。不要在插入语句中使用此列。有关更多详细信息，请参阅http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx。

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

回答by Eric Leschinski

How to insert current time into a timestamp with SQL Server:

如何使用 SQL Server 将当前时间插入时间戳：

Answer: You can't, and here's why.

答案：你不能，这就是原因。

In newer versions of SQL Server, timestampis renamed to RowVersion. Rightly so, because timestamp is very misleading.

在较新版本的 SQL Server 中，timestamp重命名为RowVersion. 没错，因为时间戳非常具有误导性。

SQL Server timestamp IS NOT set by the user and does not represent a date or time. timestamp is just a binary representation of a consecutive number, it's only good for making sure a row hasn't changed since it's been read.

SQL Server 时间戳不是由用户设置的，也不代表日期或时间。时间戳只是一个连续数字的二进制表示，它只有助于确保一行在被读取后没有改变。

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, timeor DATETIME2

如果你想存储日期或时间，不使用时间戳，则必须使用其他数据类型之一，例如像datetime，smalldatetime，date，time或者DATETIME2

For example:

例如：

create table wtf (
    id INT,
    leet timestamp
)

insert into wtf (id) values (15)

select * from wtf

15    0x00000000000007D3 

So timestamp is some kind of binary number. What if we try casting it to datetime?

所以时间戳是某种二进制数。如果我们尝试将其转换为 datetime 会怎样？

select CAST(leet AS datetime) from wtf

1900-01-01 00:00:06.677

The current year for me is not 1900. So I'm not sure what SQL Server is thinking.

我的当前年份不是 1900 年。所以我不确定 SQL Server 在想什么。

回答by Keith Liebenberg

Assume Table1 and Table2 have three columns A, B and TimeStamp. I want to insert from Table1 into Table2.

假设表 1 和表 2 具有三列 A、B 和时间戳。我想从 Table1 插入到 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

回答by Kent Weigel

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.

这是一个查询，它返回表的所有非时间戳列名称，您可以将其剪切并粘贴到插入查询中。仅供参考：189 是时间戳的类型 ID。

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.

如果要将数据从一个数据库 (DB1) 复制到另一个数据库 (DB2)，则可以使用此查询。

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

回答by joseph

create table demo ( id int, ts timestamp )

创建表演示（ id int, ts 时间戳）

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

插入演示（id，ts）值（1，默认）