Как избежать ошибки вставки метки времени при копировании данных из одной таблицы в другую?

Я пытаюсь отобрать данные в списке таблиц (~ 30) на основе первичного ключа.

Мой подход заключался в следующем: 1. Создайте промежуточную таблицу и загрузите в нее нужные данные для каждой таблицы
2. Обрежьте исходную таблицу
3. Вставьте данные из промежуточной таблицы обратно в исходную таблицу. .

Вот код, который я использую до сих пор:

declare @table nvarchar(max) 
open tab
fetch next from tab into @table
while(@@FETCH_STATUS = 0)
       begin
              print @table
             exec ('select * into ' +@table+'_intermediate from '+@table+' where P_ID in( select P_ID from pc_table )')
             exec ('truncate table '+@table)
             exec ('insert into '+@table+' select * from '+@table+'_intermediate')
             exec ('drop table '+@table+'_intermediate') 
            fetch next from tab into @table
       end
close tab
deallocate tab

Я сталкиваюсь с ошибкой:

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.

Итак, эта ошибка говорит мне, что я не могу ничего вставить в столбец меток времени.

Чтобы избежать выбора метки времени, мне нужно избегать ее выбора (т.е. использовать select *).

Есть ли простой способ выбрать все столбцы, кроме метки времени типа, или мне нужно будет перейти к информационной схеме и создать оператор динамического выбора для каждой таблицы?

(или неявный вопрос, есть ли лучший способ сделать то, что я пытаюсь сделать?)

Спасибо


sql-server-2008 sql-timestamp dml
person Rolan    schedule 26.01.2015    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Если это миллионы строк, а не миллиарды, то просто

DELETE from TABLE where P_ID not in (select P_ID from pc_table)

(возможно, партиями) может быть приемлемым. Сначала удалите все индексы (кроме первичного ключа на ID) и ограничения, удалите строки, заново создайте индексы. Еще лучше не удалять, а отключать индексы, а затем снова включать их с помощью REBUILD INDEX.

Еще одна вещь, которую следует учитывать. Если вы используете промежуточную таблицу, то после повторной ВСТАВКИ все значения столбца timestamp станут другими. Если вы не заботитесь о сохранении значений в этом столбце, вы можете просто удалить этот столбец перед обработкой и добавить его обратно после того, как все будет сделано.

Если производительность имеет значение, вы должны отключить ограничения и индексы для целевых таблиц любым выбранным вами методом.

Что подводит нас к другому методу:

SELECT * INTO intermediate_table ...

отлично работает с timestamp столбцами.

It is 

INSERT INTO final_table SELECT * FROM intermediate_table ...

это не работает со столбцами меток времени.

Таким образом, вместо TRUNCATE final_table вы можете DROP final_table и сделать SELECT * INTO final_table ... второй раз.

Таким образом, вы также сохраните значения столбца timestamp. Конечно, вам придется воссоздать все ограничения и индексы исходной таблицы, если вы полностью DROP ее.

person Vladimir Baranov    schedule 03.02.2015

arrow_upward
1
arrow_downward

Короткий ответ заключается в том, что вам нужно поместить «ноль» в любое место, где есть столбец с меткой времени.

Я сделал этот небольшой скрипт для создания списка столбцов, чтобы я поместил этот список в оператор DML:

         declare @sel_statement nvarchar(max)=''
         declare @col nvarchar(100) =''
         declare @num_rows int =0
         declare @dat_type nvarchar(30)

         declare cols cursor for
         select column_name, data_type 
         from information_schema.COLUMNS
         where TABLE_NAME = @table  --uses table fetched from tab cursor

         open cols

         fetch next from cols into @col, @dat_type
         while(@@FETCH_STATUS = 0)
                begin
                set @num_rows +=1
                if @dat_type = 'timestamp'
                     set @sel_statement += 'null'
                else  
                      set @sel_statement += @col 
                fetch next from cols into @col, @dat_type
                if @@FETCH_STATUS=0
                      set @sel_statement += ','
                end
         close cols
         deallocate cols

Это не самая красивая вещь, но она сработала.

Надеюсь, это может помочь кому-то другому, если они столкнутся с этой проблемой.

person Rolan    schedule 02.02.2015

arrow_upward
0
arrow_downward

Как насчет

delete from TABLE where P_ID not in( select P_ID from pc_table )

?

person a2800276    schedule 26.01.2015
comment
Это сработает, но при работе с миллионами строк (по крайней мере, из того, что я читал) быстрее обрезать таблицу и не заполнять файлы журнала при каждом удалении... Но, вероятно, это все же быстрее, чем копирование данные, а затем повторно вставьте их. - person Rolan; 26.01.2015
comment
Если это действительно миллионы строк и производительность имеет значение, я боюсь, что вы застряли с вводом имен столбцов. Там нет select *-кроме :) - person a2800276; 26.01.2015
comment
Это правильно. НАПЕЧАТАЙТЕ эти утверждения, создайте огромный сценарий, замените * списком столбцов, где это применимо, и запустите этот сценарий. Я бы предложил использовать транзакцию для каждой партии, чтобы сохранить ваши данные в безопасности в случае ошибки. - person Alsin; 26.01.2015