我正在尝试根据主键剔除表列表(~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 *)。
是否有一种简单的方法来选择除时间戳类型之外的所有列,或者我需要进入信息架构并为每个表构建动态选择语句?
(或者隐含的问题,有没有更好的方法来做我想做的事情?
谢谢
如果是数百万行,而不是数十亿行,那么简单
DELETE from TABLE where P_ID not in (select P_ID from pc_table)
(可能是分批的)可能是可以接受的。首先删除所有索引(ID
上的主键除外)和约束,删除行,重新创建索引。更好的是,不要删除,而是禁用索引,然后使用 REBUILD INDEX
重新启用它们。
还有一件事需要考虑。如果您确实使用中间表,那么在重新插入后,timestamp
列的所有值将变得不同。如果您不关心保留此列中的值,只需在处理之前删除此列,然后在所有操作完成后将其添加回来。
如果性能很重要,则应以您选择的任何方法禁用目标表上的约束和索引。
这给我们带来了另一种方法:
SELECT * INTO intermediate_table ...
与timestamp
列配合使用很好。
是的
INSERT INTO final_table SELECT * FROM intermediate_table ...
这不适用于时间戳列。
因此,与其TRUNCATE final_table
,您还可以DROP final_table
并第二次执行SELECT * INTO final_table ...
。
因此,您还将保留timestamp
列的值。当然,如果您完全DROP
原始表的所有约束和索引,则必须重新创建它。
简短的回答是,您需要在存在时间戳列的任何位置放置"null"。
我制作了这个小脚本来创建列列表,以便将该列表放入 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
这不是有史以来最漂亮的东西,但它奏效了。
希望如果他们遇到这个问题,这可以帮助其他人。
怎么样
delete from TABLE where P_ID not in( select P_ID from pc_table )
?