In SQL Server 2008, how should I copy data from database to another database?

Question

I'm trying to write a stored procedure to copy a subset of data from one set of tables to an identical set of tables in a different database. The "source" database needs to be a parameter to the stored procedure.

I've struggled with this for two days now, and I thought I had a good solution:

1. Validate that the schemas are the same.
2. Create temporary "rmt" synonyms for the source tables using dynamic SQL.
3. Copy the data using INSERT INTO A SELECT * FROM rmtA WHERE <criteria>
4. Delete the synonyms.

This works pretty well for most tables, but for tables that contain an identity column, I'm forced not only to SET IDENTITY_INSERT ON & OFF, but even worse, I can't use SELECT *; I have to specify all the columns explicitly. This will be a nightmare if I add or delete columns later.

I've gotta get something out the door, so I'm going with this solution for now, but I'd like to think that there's a better solution out there somewhere.

Help?

Answer 1

It sounds like you're using dynamic SQL in your stored procedure, so you're ready to dynamically create your list of columns in the SELECT clause.

You can select from sys.columns to get the list of columns, and learn if the table has an identity column. Here's a query that shows the information you need to create the list of columns.

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

In short, if is_identity is 1 for at least one column, you'll need to include the SET IDENTITY_INSERT. And, you would exclude any columns from the SELECT clause where is_identity = 1.

And, this approach will adapt to new columns you add to the tables.

Here's an example

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