如何避免问题无法在尝试插入数据时将显式值插入时间戳列？ [英] How to avoid issue cannot insert an explicit value into a timestamp column on trying to insert data ?
本文介绍了如何避免问题无法在尝试插入数据时将显式值插入时间戳列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何避免问题无法将显式值插入时间戳列。使用INSERT和列列表来排除时间戳列，或者在尝试将数据插入表时将DEFAULT插入时间戳列？
我实际上是在插入数据到具有标识的表插入到从另一个DB中的类似类型的表中获取的DB。我遇到了具有数据类型为TimeStamp的列的表的问题。
错误如下：
不能插入显式值 timestamp 列。 使用 INSERT a 列列表 排除 timestamp column ，或 insert a DEFAULT timestamp column 。
插入 所有其他表都很好。我是使用通用脚本 下面的 。
< pre lang = SQL>脚本 如下：
& lt; pre lang =& quot; sql& ; QUOT;&安培; GT;
/ * 用于插入现有归档数据库的一次性脚本从临时归档DB中获取已存在的数据* /
BEGIN
DECLARE < span class =code-sdkkeyword> @ Query NVARCHAR （MAX）
- 传递/设置存档数据库（现有存档数据库）
DECLARE @ ArchivalDB NVARCHAR （MAX）
SET @ ArchivalDB =&＃39; FTS_DIB_ARCH3&＃39;
- 通过/设置临时存档数据库（Temp Archival DB临时存储现有数据） ）
DECLARE @ TempArchivalDB NVARCHAR （MAX）
SET @ TempArchivalDB =&＃39; FTS_DIB_ARCH2&＃39;
- 传递/设置架构名称
DECLARE @ TABLE_SCHEMA NVARCHAR （MAX）
SET @ TABLE_SCHEMA =&＃39; dbo&＃39;
DECLARE @ TableName NVARCHAR （MAX）
BEGIN TRY
- 创建一个名为IDENTITYINSERTOFF的Cursor来获取存档数据库中的所有标识插入表
- 并将表逐个传递给参数@TableName
DECLARE IDENTITYINSERTOFF CURSOR FOR
选择 TABLE_NAME
来自 INFORMATION_SCHEMA.COLUMNS
其中 TABLE_SCHEMA =&＃39; DBO&＃39;
和 COLUMNPROPERTY （object_id（TABLE_NAME），COLUMN_NAME，&＃39; IsIdentity&＃ 39;）= 1
ORDER BY ORDINAL_POSITION
OPEN IDENTITYINSERTOFF
获取 next 从 IDENTITYINSERTOFF 到
@ TableName
- 结束
WHILE @@ FETCH_STATUS = 0
BEGIN
- PRINT @ArchivalDB
- 使用上面传递的架构名称插入现有存档数据库
SET @ Query = N&＃39; INSERT INTO &＃39; + @ ArchivalDB +&＃39;。&＃39; + @ TABLE_SCHEMA +&＃39;。&＃39; + @ TableName +& ＃39;（&＃39 ;;
- 为每个标识插入传递列表
SELECT @ Query = @ Query + QUOTENAME（COLUMN_NAME）+ N& ;＃39;，&＃39;
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @ TableName
ORDER BY ORDINAL_POSITION ;;
- 结束
- 在插入查询末尾修剪逗号
SET < /跨度> @Query = LEFT （ @ Query ， NULLIF （LEN（ @ Query ） - 1，-1））
- 关闭动态创建表语法的大括号
SET @Query = @ Query + N&＃39;）&＃39 ;;
- 从临时存档数据库（带有标识插入关闭的表）中选择数据&＃ 39;
SELECT @ Query = @Query + N&＃39; SELECT &＃39 ;;
- 为每个标识插入表传递列
SELECT @ Query = @ Query + QUOTENAME（COLUMN_NAME）+ N&＃39; ，&安培;＃39;
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @ TableName
ORDER BY ORDINAL_POSITION ;;
- 已结束
- - 在从查询中选择的末尾修剪逗号
SET @Query = LEFT （ @ Query ， NULLIF （ LEN（ @ Query ） - 1，-1））
- 从上面传递的模式名称的Temp Archive DB中选择（暂时存储现有数据）
SET @ Query = + @ Query + N&＃39; FROM &＃39; + @ TempArchivalDB +&＃39;。&＃39; + @ TABLE_SCHEMA +&＃39;。&＃39; + @ TableName ;
- PRINT&＃39; K&＃39;
- 打印插入以从查询中选择
PRINT @ Query
- 执行从查询中选择插入（从Termp存档表中将数据保存到现有存档数据库）
EXECUTE sp_executesql @ Query
- 已结束
- 从Cursor中获取标题为IDENTITYINSERTOFF的下一个值
获取下一步来自 IDENTITYINSERTOF F into
@ TableName
- 已结束
END
- 已结束
- 关闭并释放名为IDENTITYINSERTOFF的光标
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
- 已结束
END TRY
BEGIN CATCH
DECLARE @ errMsg AS VARCHAR （MAX）
SET @ errMsg = ERROR_MESSAGE（）
- PRINT @errMsg
- Close和Deallocate Cursor命名为IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
- 已结束
- - 如果有任何问题，请回滚事务
- ROLLBACK TRAN
- 已结束
END CATCH
END
& ; lt; / pre& gt;< / pre>
我尝试了什么：
脚本如下：
/ * 一次性插入脚本进入现有档案数据库从临时档案DB中获取现有数据* /
BEGIN
< span class =code-keyword> DECLARE @ Query NVARCHAR （MAX）
- 通过/设置档案数据库（现有档案数据库）
DECLARE @ ArchivalDB NVARCHAR （MAX）
SET @ ArchivalDB = ' FTS_DIB_ARCH3'
- Pass / Set T emp Archival DB（临时存储现有数据的Temp Archival DB）
DECLARE @ TempArchivalDB NVARCHAR （MAX）
SET @ TempArchivalDB = ' FTS_DIB_ARCH2'
- 传递/设置架构名称
DECLARE @ TABLE_SCHEMA NVARCHAR （MAX）
SET @ TABLE_SCHEMA = ' dbo'
DECLARE @ TableName NVARCHAR （MAX）
BEGIN TRY
- 创建一个名为IDENTITYINSERTOFF的游标，以获取存档数据库中的所有标识插入表
- 并将表逐个传递给参数@TableName
DECLARE IDENTITYINSERTOFF CURSOR FOR
选择 TABLE_NAME
来自 INFORMATION_SCHEMA.COLUMNS
其中 TABLE_SCHEMA = ' DBO'
和 COLUMNPROPERTY （object_id（TABLE_NAME），COLUMN_NAME，' IsIdentity'）= 1
ORDER BY ORDINAL_POSITION
OPEN IDENTITYINSERTOFF
获取下一步从 IDENTITYINSERTOFF 进入
@ TableName
- 已结束
WHILE @@ FETCH_STATUS = 0
BEGIN
- PRINT @ArchivalDB
- 使用上面传递的架构名称插入现有存档数据库
SET @查询 = N ' INSERT INTO' + @ ArchivalDB + ' 。' + @ TABLE_SCHEMA + ' 。' + @ TableName + ' （';
- 为每个标识插入表传递列
< span class =code-keyword> SELECT @ Query = @ Query + QUOTENAME（COLUMN_NAME）+ N ' ，'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @ TableName
ORDER < span class =code-keyword> BY ORDINAL_POSITION ;;
- 已结束
- - 在插入查询末尾修剪逗号
SET @ Query = LEFT （ @ Query ， NULLIF （LEN （ @ Query ） - 1，-1））
- 关闭动态创建表语法的大括号
SET @ Query = @ Query + N ' ）'< /跨度>;
- 从临时存档数据库（具有标识插入关闭的表）中选择数据'
SELECT @ Query = @查询 + N ' SELECT';
- 为每个标识插入表传递列
SELECT @ Query = @ Query + QUOTENAME（COLUMN_NAME）+ N ' ，'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @ TableName
ORDER BY ORDINAL_POSITION ;;
- 已结束
- - 在从查询中选择的末尾修剪逗号
SET @Query = LEFT （ @ Query ， NULLIF （ LEN（ @ Query ） - 1，-1））
- 从上面传递的模式名称的Temp Archive DB中选择（暂时存储现有数据）
SET @ Query = + @ Query + N ' FROM' + @TempArchivalDB + ' 。' + @ TABLE_SCHEMA + ' 。' + @ TableName ;
- PRINT'K'
- 打印插入到从查询中选择
PRINT @ Query
- 执行插入以从查询中选择（从Termp存档表中将数据保存到现有存档数据库）
EXECUTE sp_executesql @ Query
- 已结束
- 从标题为IDENTITYINSERTOFF的光标中获取下一个值
获取下一步来自 IDENTITYINSERTOFF into
@ TableName
- 已结束
END
- 已结束
- 关闭并释放名为IDENTITYINSERTOFF的游标
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
- 已结束
END TRY
BEGIN CATCH
DECLARE @ errMsg AS VARCHAR （MAX）
SET @ errMsg = ERROR_MESSAGE（）
- PRINT @errMsg
- 关闭并释放游标命名IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
< span class =code-comment> - 已结束
- RollBack事务如果有任何问题
- ROLLBACK TRAN
- 已结束
END CATCH
END
解决方案
如果是我您要保留原始时间戳，然后将存档数据库中时间戳列的列类型更改为varbinary（8），您应该可以直接复制数据。
如果你不介意使用新的时间戳，那么在将行插入目标表时使用单词DEFAULT。
演示创建 table tt的简单示例
（
ts timestamp ，
i int ，
v varchar
）
我可以插入这样的值
< pre lang =SQL> INSERT INTO tt VALUES （ DEFAULT ， 1 ，' 1）
INSERT INTO tt VALUES （ DEFAULT ， 2 ，' 2'）或者像这样
INSERT INTO tt（i，v） VALUES （ 3 ，' 3'）
INSERT INTO tt（i，v） VALUES （< span class =code-digit> 4 ，' 4'）
如果我使用varbinary（8）创建该表的归档表，等同于timestamp列create table tt_archive
（
ts varbinary（8），
i in t，
v varchar
）我可以使用
insert 进入 tt_archive 选择 * 来自 tt
请注意，您将无法使用存档表中的时间戳功能
< b>为了说明，这里是一个包含时间戳列的表：
CREATE TABLE团队（
TeamID INT NOT NULL PRIMARY KEY IDENTITY（1,1），
TeamName VARCHAR（50），
TIMESTAMP）
在此表中插入新行并指定timestamp列的值将产生此错误：
INSERT INTO团队（TeamName，Timestamp）
VALUES（'迈阿密马林鱼'，@ @ DBTS）
错误消息：
消息273，级别16，状态1，行1
无法在时间戳列中插入显式值。将INSERT与列
列表一起使用以排除时间戳列，或将DEFAULT插入时间戳列。
解决方案：
有两种解决此问题的方法，如错误消息所示。
第一种方法：
第一种方法是不在INSERT语句中指定的列列表中包含timestamp列：
INSERT INTO团队（TeamName）
VALUES（'纽约洋基队'）
第二种方法：
第二种方法是将DEFAULT指定为timestamp列中的值，如下面的语句所示：
INSERT INTO Team（TeamName，Timestamp）
VALUES（'Miami Marlins'， DEFAULT）
How to avoid issue 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 while trying to insert data to a table?
I am actually inserting data to tables with identity insert to a DB fetched from similar type of tables in another DB. I am getting issue for tables having column with datatype TimeStamp.
The error is as below:
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.
Insertion to all other tables are fine.I am using generic script for the same as below .
<pre lang="SQL">The script is as below:
<pre lang="sql">
/* One Time Script for Inserting into Existing Archival DB fetching already existing data from Temporary Archival DB*/
BEGIN
DECLARE @Query NVARCHAR(MAX)
--Pass/Set Archival DB(Existing Archival DB)
DECLARE @ArchivalDB NVARCHAR(MAX)
SET @ArchivalDB='FTS_DIB_ARCH3'
--Pass/Set Temp Archival DB(Temp Archival DB where existing data is stored temporarily)
DECLARE @TempArchivalDB NVARCHAR(MAX)
SET @TempArchivalDB='FTS_DIB_ARCH2'
--Pass/Set Schema Name
DECLARE @TABLE_SCHEMA NVARCHAR(MAX)
SET @TABLE_SCHEMA= 'dbo'
DECLARE @TableName NVARCHAR(MAX)
BEGIN TRY
--Created a Cursor named IDENTITYINSERTOFF to fetch all Identity Insert Tables IN Archival DB
--and Pass the Table one by one to Parameter @TableName
DECLARE IDENTITYINSERTOFF CURSOR FOR
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA ='DBO'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY ORDINAL_POSITION
OPEN IDENTITYINSERTOFF
Fetch next from IDENTITYINSERTOFF into
@TableName
--Ended
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ArchivalDB
--Insert into Existing Archive DB with Schema Name passed above
SET @Query = N'INSERT INTO ' + @ArchivalDB+'.'+ @TABLE_SCHEMA+'.'+ @TableName + '(';
--Pass Columns for each Identity Insert Tables
SELECT @Query= @Query + QUOTENAME(COLUMN_NAME) + N' ,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;;
--Ended
--Trim the Comma at the end of Insert Query
SET @Query= LEFT(@Query, NULLIF(LEN(@Query)-1,-1))
-- Close the Brace of the Dynamic Create Table Syntax
SET @Query = @Query + N')';
--Select from Temp Archive DB (Tables with Identity Insert Off) with data'
SELECT @Query = @Query + N'SELECT ';
--Pass Columns for each Identity Insert Tables
SELECT @Query= @Query + QUOTENAME(COLUMN_NAME) + N' ,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;;
--Ended
--Trim the Comma at the end of Select from Query
SET @Query= LEFT(@Query, NULLIF(LEN(@Query)-1,-1))
--Select from Temp Archive DB with Schema Name passed above (where existing data is stored temporarily)
SET @Query= + @Query + N' FROM ' + @TempArchivalDB+'.'+ @TABLE_SCHEMA+'.'+ @TableName ;
--PRINT 'K'
--To Print the Insert into Select From Query
PRINT @Query
-- Execute the Insert into Select From Query(Saving data to Existing Archival DB from Termp Archival Tables)
EXECUTE sp_executesql @Query
--Ended
--Fetch the next value from the Cursor titled IDENTITYINSERTOFF
Fetch next from IDENTITYINSERTOFF into
@TableName
--Ended
END
--Ended
--Close and Deallocate Cursor named IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
--Ended
END TRY
BEGIN CATCH
DECLARE @errMsg AS VARCHAR(MAX)
SET @errMsg = ERROR_MESSAGE()
--PRINT @errMsg
--Close and Deallocate Cursor named IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
--Ended
--RollBack the Transaction if there is any Issue
--ROLLBACK TRAN
--Ended
END CATCH
END
</pre></pre>
What I have tried:
The script is as below:
/* One Time Script for Inserting into Existing Archival DB fetching already existing data from Temporary Archival DB*/
BEGIN
DECLARE @Query NVARCHAR(MAX)
--Pass/Set Archival DB(Existing Archival DB)
DECLARE @ArchivalDB NVARCHAR(MAX)
SET @ArchivalDB='FTS_DIB_ARCH3'
--Pass/Set Temp Archival DB(Temp Archival DB where existing data is stored temporarily)
DECLARE @TempArchivalDB NVARCHAR(MAX)
SET @TempArchivalDB='FTS_DIB_ARCH2'
--Pass/Set Schema Name
DECLARE @TABLE_SCHEMA NVARCHAR(MAX)
SET @TABLE_SCHEMA= 'dbo'
DECLARE @TableName NVARCHAR(MAX)
BEGIN TRY
--Created a Cursor named IDENTITYINSERTOFF to fetch all Identity Insert Tables IN Archival DB
--and Pass the Table one by one to Parameter @TableName
DECLARE IDENTITYINSERTOFF CURSOR FOR
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA ='DBO'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY ORDINAL_POSITION
OPEN IDENTITYINSERTOFF
Fetch next from IDENTITYINSERTOFF into
@TableName
--Ended
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ArchivalDB
--Insert into Existing Archive DB with Schema Name passed above
SET @Query = N'INSERT INTO ' + @ArchivalDB+'.'+ @TABLE_SCHEMA+'.'+ @TableName + '(';
--Pass Columns for each Identity Insert Tables
SELECT @Query= @Query + QUOTENAME(COLUMN_NAME) + N' ,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;;
--Ended
--Trim the Comma at the end of Insert Query
SET @Query= LEFT(@Query, NULLIF(LEN(@Query)-1,-1))
-- Close the Brace of the Dynamic Create Table Syntax
SET @Query = @Query + N')';
--Select from Temp Archive DB (Tables with Identity Insert Off) with data'
SELECT @Query = @Query + N'SELECT ';
--Pass Columns for each Identity Insert Tables
SELECT @Query= @Query + QUOTENAME(COLUMN_NAME) + N' ,'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;;
--Ended
--Trim the Comma at the end of Select from Query
SET @Query= LEFT(@Query, NULLIF(LEN(@Query)-1,-1))
--Select from Temp Archive DB with Schema Name passed above (where existing data is stored temporarily)
SET @Query= + @Query + N' FROM ' + @TempArchivalDB+'.'+ @TABLE_SCHEMA+'.'+ @TableName ;
--PRINT 'K'
--To Print the Insert into Select From Query
PRINT @Query
-- Execute the Insert into Select From Query(Saving data to Existing Archival DB from Termp Archival Tables)
EXECUTE sp_executesql @Query
--Ended
--Fetch the next value from the Cursor titled IDENTITYINSERTOFF
Fetch next from IDENTITYINSERTOFF into
@TableName
--Ended
END
--Ended
--Close and Deallocate Cursor named IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
--Ended
END TRY
BEGIN CATCH
DECLARE @errMsg AS VARCHAR(MAX)
SET @errMsg = ERROR_MESSAGE()
--PRINT @errMsg
--Close and Deallocate Cursor named IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF
DEALLOCATE IDENTITYINSERTOFF
--Ended
--RollBack the Transaction if there is any Issue
--ROLLBACK TRAN
--Ended
END CATCH
END 解决方案
If it is important to you to retain the original timestamp then change the column types of the timestamp columns on your archive database tovarbinary(8)and you should be able to copy the data straight across.
If you don't mind using a new timestamp then use the word DEFAULT when inserting rows into the target table.
A trivial example to demonstratecreate table tt ( ts timestamp, i int, v varchar )
I can insert values like this
INSERT INTO tt VALUES(DEFAULT, 1, '1') INSERT INTO tt VALUES(DEFAULT, 2, '2')or like this
INSERT INTO tt (i,v) VALUES( 3, '3') INSERT INTO tt (i,v) VALUES( 4, '4')
If I create an archive table for that table using varbinary(8) for the equivalent of the timestamp columncreate table tt_archive ( ts varbinary(8), i int, v varchar )I can copy the data over to it using this
insert into tt_archive select * from tt
Note that you won't be able to use the timestamp functionality in the archive table
To illustrate, here’s a table that contains a timestamp column:
CREATE TABLE Team (
TeamID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
TeamName VARCHAR(50),
TIMESTAMP )
Inserting a new row in this table and specifying a value to the timestamp column will generate this error:
INSERT INTO Team ( TeamName,Timestamp )
VALUES ( 'Miami Marlins', @@DBTS )
Error Message:
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.
SOLUTION:
There are 2 ways of working around this issue, as the error message suggests.
First method:
The first method is not to include the timestamp column in the list of columns specified in the INSERT statement:
INSERT INTO Team ( TeamName )
VALUES ( 'New York Yankees' )
Second Method:
The second method is to specify DEFAULT as the value in the timestamp column, as can be seen in the following statement:
INSERT INTO Team ( TeamName, Timestamp )
VALUES ( 'Miami Marlins', DEFAULT )这篇关于如何避免问题无法在尝试插入数据时将显式值插入时间戳列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
