Script for copying data between databases

data-duplicator.sql

/*
Script for copying data between databases

Howto:

    * Duplicate your database schema, but with all the FK constraints and identity specs removed (see below).
    * Update the config at the top of this file.
    * Connect to the target database.
    * Run this script.

To generate an unrestrictd empty schema to put the data into:

    * ssms > databases > right-click your_db > tasks > generate scripts ...
        * next
            * advanced
                * change "Script Foreign Keys" to false (will avoid having
                  to get the insertion order correct)
            * pick somwhere to save the resultant sql file
            * unicode text
        * next > next > finish
    * open the second schema sql file in ssms
    * switch to sqlcmd mode: "Query" > "SQLCMD Mode"
    * find and replace:
        * "your_db" (or whatever the name is) with "$(database)"
        * "IDENTITY(1,1) " with an empty string
    * add ":setvar database your_duplicate_db" to the top of the script (or another name if you prefer)
    * save
    * run the result
    * refresh the database list in object explorer, you should see the new db
*/

-- config; change these:

use your_duplicate_db; -- target for copy operation

declare @sourceDb sysname = 'your_db'; -- source for copy operation; change this to your source database name

--------

declare @sql nvarchar(max);

-- compile a big sql query that will copy the data for all the tables listed in the where clause into the currently selected database
set @sql = (
select
--tbl.name,
'print ''copying data from: ' + quotename(tbl.name) + '''; insert into ' + quotename(tbl.name) + ' (' +
    substring( (select ', ' + quotename(column_name) from information_schema.columns where table_name = tbl.name
        and data_type <> 'timestamp' -- can't insert data into a timestamp/rowversion column
    order by ordinal_position for xml PATH('')), 3, 200000) +
') select ' +
    substring( (select ', ' + quotename(column_name) from information_schema.columns where table_name = tbl.name
        and data_type <> 'timestamp' -- can't insert data into a timestamp/rowversion column
    order by ordinal_position for xml PATH('')), 3, 200000) +
' from ' + @sourceDb + '..' + quotename(tbl.name) + '; '
from sys.tables tbl
where tbl.name in (
    -- this list generated by below query, unwanted tables commented out by hand
    'sometable', 
    --'some-ignored-table', 
    'someothertable', 
    'widgets'
)
order by tbl.name
for xml path('')
);

-- run it!
exec sp_executesql @sql;

---- copy-able table list for the above where-clause
--select '''' + name + ''', '
--from sys.tables
--where schema_id = 1 -- dbo
--order by name
--;

-- refs:
-- get a list of the columns for this table as comma separated escaped strings http://stackoverflow.com/a/2005474/10245