sql-server - SQL Server：安全地将数据移动到（维护不善的）历史表

Question

我刚开始工作的公司有一张表格和一张历史表格。然而，历史表维护不善，因此与原始表不同。

问题之一是历史表的列的顺序不同（尽管它们确实具有相同的名称）。另一个是缺少一些列。

目前每年一次，表中的数据通过如下查询移动到历史记录（删除语句省略）：

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

该表有一百多列，因此指定所有名称既繁琐又容易出错。此外，如果有人更改表，而不是历史表或移动值的过程，则值将丢失而不会出现任何错误。这种情况发生了很多，我正试图在未来防止这种情况发生。

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

查询

insert into my_history_table
select * from my_table;

根据位置插入，并在存在不兼容的类型或缺少列时给出错误。但是，如果两个兼容的列（例如两个 int）交换位置，它会起作用，但数据最终会出现在错误的列中。

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

有没有办法做到这一点？

标签： sql-server

Answer 1

您可以动态构建插入字符串，例如：

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