sql - 在SQL Server 2008中，我应该如何将数据从一个数据库复制到另一个数据库？

Question

我正在尝试编写一个存储过程，将数据子集从一组表复制到不同数据库中的一组相同的表。 “源”数据库需要成为存储过程的参数。

我已经为此苦苦挣扎了两天，我认为我有一个很好的解决方案:

1. 验证模式是否相同。
2. 使用动态 SQL 为源表创建临时“rmt”同义词。
3. 使用 INSERT INTO A SELECT * FROM rmtA WHERE 复制数据
4. 删除同义词。

这对大多数表来说效果很好，但对于包含标识列的表，我不仅被迫 SET IDENTITY_INSERT ON & OFF，更糟糕的是，我不能使用 SELECT *;我必须明确指定所有列。如果我稍后添加或删除列，这将是一场噩梦。

我必须得到一些东西，所以我现在使用这个解决方案，但我想在某个地方有更好的解决方案。

帮忙吗？

Answer 1

听起来您正在存储过程中使用动态 SQL，因此您已准备好在 SELECT 子句中动态创建列列表。

您可以从 sys.columns 中选择以获取列列表，并了解该表是否具有标识列。下面的查询显示了创建列列表所需的信息。

SELECT c.name, is_identity
FORM sys.columns c
WHERE object_id = object_id('MyTable')

简而言之，如果至少一列的 is_identity 为 1，则您需要包含 SET IDENTITY_INSERT。而且，您将从 SELECT 子句中排除 is_identity = 1 的任何列。

而且，这种方法将适应您添加到表中的新列。

举个例子

DECLARE @TableName varchar(128) = 'MyTableName'
DECLARE @ColumnName varchar(128)
DECLARE @IsIdentity bit
DECLARE @TableHasIdentity bit = 0
DECLARE @sql varchar(2000) = 'SELECT '

-- create cursor to step through list of columns
DECLARE MyCurs CURSOR FOR
SELECT c.name, is_identity
FROM sys.columns c
WHERE object_id = object_id(@TableName)
ORDER BY column_id

-- open cursor and get first row
OPEN MyCurs
FETCH NEXT FROM MyCurs INTO @ColumnName, @IsIdentity

-- process each column in the table
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @IsIdentity = 0
-- add column to the SELECT clause
        SET @sql = @sql + @ColumnName + ', '
    ELSE
-- indicate that table has identity column  
        SET @TableHasIdentity = 1

-- get next column
    FETCH NEXT FROM MyCurs INTO @ColumnName, @IsIdentity
  END

-- cursor cleanup
CLOSE MyCurs
DEALLOCATE MyCurs

-- add FROM clause
SET @sql = LEFT(@sql, LEN(@sql)-1) + CHAR(13) + CHAR(10) + 'FROM ' + @TableName

-- add SET IDENTITY if necessary
IF @TableHasIdentity = 1
    SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON' + CHAR(13) + CHAR (10)
                + @sql + CHAR(13) + CHAR (10)
                + 'SET IDENTITY_INSERT ' + @TableName + ' OFF'

PRINT @sql