Как избежать проблемы не удается вставить явное значение в столбец временной метки при попытке вставить данные ?
Как избежать проблемы невозможно вставить явное значение в столбец временной метки. Используйте вставку со списком столбцов, чтобы исключить столбец timestamp, или вставьте значение по умолчанию в столбец timestamp при попытке вставить данные в таблицу?
На самом деле я вставляю данные в таблицы с помощью identity insert в БД, полученную из таблиц аналогичного типа в другой БД. Я получаю проблему для таблиц, имеющих столбец с меткой времени типа данных.
Ошибка выглядит следующим образом:
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>
Что я уже пробовал:
Сценарий выглядит следующим образом:
/* 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
Richard Deeming
Мы должны угадать, какую "проблему" вы получаете?
Нажмите кнопку "улучшить вопрос" и добавьте правильное описание проблемы. Включите полную информацию о любых сообщениях об ошибках.
phil.o
Поле метки времени автоматически заполняется значением метки времени при создании записи.
Таким образом, столбцы меток времени не должны назначаться во время команд вставки или обновления.
Вы должны фильтровать их так, чтобы они не обрабатывались.