代码之家 › 专栏 › 技术社区 › The Oddler

SQL Server:安全地将数据移动到(维护不好的)历史表中

sql-server

The Oddler · 技术社区 · 8 年前

我刚开始工作的那家公司有一张桌子和一张历史记录表。但是历史表维护得不好,所以它与原始表不一样。

其中一个问题是历史表的列的顺序不同(尽管它们的名称确实相同)。另一个是缺少一些列。

当前每年一次,表中的数据通过类似查询(删除语句省略)移动到历史记录中:

insert into my_history_table (a, b, c)
select a, b, c from my_table;

这个表有100多列,因此指定所有的名称是冗长和容易出错的。另外,如果有人更改了表,而不是历史表或移动值的过程,则值将丢失而不会出现任何错误。这种情况已经发生了很多,我正在努力防止将来发生这种情况。

那么,是否可以根据列名而不是它们的位置将数据从一个表移动到另一个表,同时检查它们是否一致?

询问

insert into my_history_table
select * from my_table;

基于位置插入,当存在不兼容的类型或缺少列时出错。但是,如果两个兼容的列(例如两个int)交换位置,它将工作,但数据最终将位于错误的列中。

遗憾的是,我不能保证没有人会在我背后更改原始表,但我几乎可以保证,更改该表的人不会相应地更新查询和历史表。由于这个查询一年只运行一次,如果历史表需要更新,它会给出一个错误。

有办法吗?

3 回复 | 直到 8 年前

JMabee 8 年前

可以动态生成插入字符串,例如:

CREATE TABLE ProdTable(id int, col1 int, col2 int, col3 int)
CREATE TABLE HistoryTable(id int, col3 int, col1 int, col2 int)


IF EXISTS(
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'ProdTable'
EXCEPT
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'HistoryTable')

RAISERROR( 'THERE ARE COLUMNS IN THE MAIN TABLE THAT DO NOT EXIST IN THE HISTORY TABLE', 16,1)

DECLARE @COL_NAME varchar(40)
DECLARE @INSERT_STRING varchar(max)
DECLARE @SELECT_STRING varchar(max)

SET @INSERT_STRING = 'INSERT INTO HistoryTable('
SET @SELECT_STRING = ' SELECT '

DECLARE csr CURSOR LOCAL FORWARD_ONLY  for
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'HistoryTable'

OPEN csr
FETCH csr INTO @COL_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @INSERT_STRING = @INSERT_STRING + '[' + @COL_NAME + '],'
SET @SELECT_STRING = @SELECT_STRING + '[' + @COL_NAME + '],'
FETCH csr INTO @COL_NAME
END
CLOSE csr
DEALLOCATE csr

SET @INSERT_STRING = SUBSTRING(@INSERT_STRING,0,LEN(@INSERT_STRING))
SET @SELECT_STRING = SUBSTRING(@SELECT_STRING,0,LEN(@SELECT_STRING))

SET @INSERT_STRING = @INSERT_STRING + ') ' + CHAR(10)  + @SELECT_STRING + ' FROM ProdTable ' 

PRINT @INSERT_STRING

如果您想看到它抛出错误,那么只需向ProdTable添加另一列:

ALTER TABLE ProdTable ADD  col4 INT

只需将“消息”选项卡中的生成字符串剪切并粘贴到另一个查询窗口中,您就可以开始了。显然,如果要做大量的行,就应该分批进行……但这是另一个线程。

编辑:

您可以不用光标,只需使用FOR XML:

IF EXISTS(
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'ProdTable'
EXCEPT
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'HistoryTable')

RAISERROR( 'THERE ARE COLUMNS IN THE MAIN TABLE THAT DO NOT EXIST IN THE HISTORY TABLE', 16,1)

DECLARE @COL_NAMES varchar(MAX)
DECLARE @INSERT_STRING varchar(max)

SELECT @COL_NAMES =  (SELECT  '[' + Column_Name + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'HistoryTable'
FOR XML PATH(''))

SET @COL_NAMES = SUBSTRING(@COL_NAMES,0,LEN(@COL_NAMES))
SET @INSERT_STRING = 'INSERT INTO HistoryTable(' + @COL_NAMES + ') ' + CHAR(10)  +   ' SELECT ' + @COL_NAMES  + ' FROM ProdTable ' 

PRINT @INSERT_STRING

lokesh 8 年前

你有两个选择。我有相似的要求,我在不同的情况下都实现了。

使用C 35;.NET SQLBulkCopy类,它提供了基于列名映射列的功能。
动态查询。下面是一个粗略的伪代码。

从信息架构列中选择* 其中TABLE_NAME='主表'

“分析每个源列”时开始选择@Col FROM INFORMATION\u SCHEMA.COLUMNS 其中TABLE_NAME='History TABLE'和ColumnName='MainTableColumn'

如果@Col不为空开始 string@ColumnList+=@Col列结束

--然后开发如下的动态查询

插入历史表(@ColumnList) 从主表中选择@ColumnList

结束

-1

Ashish Mishra 8 年前

请使用触发器而不是直接在历史表中插入数据。使用trigger,这样如果任何DML操作被执行,trigger将在magic表的帮助下自动在历史表中插入数据。