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

Question

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

我这个两天现在挣扎，我想我有一个很好的解决方案：

1. 验证的架构是相同的。
2. 使用动态SQL为源表创建临时“rmt”同义词。
3. 使用INSERT INTO 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