Generate insert statements for table AND related data

I'm looking to move data from tables and related tables between 2 databases. The trouble is the primary keys are out of sync.

Is anyone aware of a tool which can generate insert statements for a given table and it's related data (perhaps walking foreign keys)?

I've been looking at SSMS toolpack, RedGate's tools, Database project data compare, various open source data diff tools, etc etc, but not found anything.

I'm aware of being able to walk foreign keys, so perhaps some amazingly clever SQL script might be able to do it?

Seeing as I've looked at so many tools, I'm guessing it's probably blooming difficult / impossible to do it fully automated. But perhaps a technique that needs some manual intervention as well?

Or perhaps anyone has hints on how they handle this sort of task. I don't really fancy writing lots of manual sql to handle the insert - as it's bound to be error prone.

What I'm really hoping for is a way of generating SQL to tie up the PK and FK's whilst doing the inserts in the new database.

Say for example we had:

+--------------+--------------+
|     personid |     name     |
+--------------+--------------+
|            1 |         bob  |
|            2 |         fred |
+--------------+--------------+

+----------+----------------+----------+
| animalid |     animal     | PersonID |
+----------+----------------+----------+
|        1 |         cat    |        1 |
|        2 |         dog    |        1 |
|        3 |         lizard |        2 |
+----------+----------------+----------+

It would be awesome to copy bob to the new database and bob's cat and dog. Then fred and fred's lizard.

The SSMS Tools Pack allows you to generate insert scripts for a particular table. On the Features page, it's the "Generate Insert statements from resultsets, tables or databases." That won't get all child tables automatically, however.

In an older Stack Overflow post, they mention a few methods for generating inserts. The first method (using SSMS's Generate Scripts functionality) will work if you have SQL 2005 or better. The second method, a downloaded stored procedure, could do the job if you're using 2000.

Assuming you have 2005 or later, right-click on the database and go to Tasks --> Generate Scripts. From there, you can select specific database objects. You'll select all of the necessary tables (so this is the manual process).

To get the list of tables, you are correct about walking the foreign key list. That's at sys.foreign_keys. The following script will get a list of all foreign key dependencies from a central point. Note that this does not get the child records, but only the "parent" records. For example, suppose T3 has a foreign key (T2ID) to T2, and T2 has a foreign key (T1ID) to T1. If we run this script on T3, it will return three tables: T3, T2, and T1. If we run it on T2, it will return two tables: T2 and T1. If we run it on T1, it will return only T1.

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 't2';

with foreignkeys as
(
    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName,
        convert(varchar(8000), tbls.name + '.' + tbl.name) as [Path]
    from 
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
    where
        tbls.name = @SchemaName
        and tbl.name = @TableName

    UNION ALL

    select
        ref.object_id as ObjectID,
        refs.name as SchemaName,
        ref.name as TableName,
        convert(varchar(8000), fks.[Path] + '/' + refs.name + '.' + ref.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables ref on fk.referenced_object_id = ref.object_id
        inner join sys.schemas refs on ref.schema_id = refs.schema_id
        inner join foreignkeys fks on fks.objectid = fk.parent_object_id
    where
        fks.objectid <> ref.object_id       
        AND fks.[Path] NOT LIKE '%' + refs.name + '.' + ref.name + '%'
)
select 
    SchemaName + '.' + TableName
from 
    foreignkeys

UNION

select @SchemaName + '.' + @TableName;

If you want to get the child tables as well (i.e., getting the entire foreign key dependency chain), another CTE will do it:

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 't2';

with foreignkeys as
(
    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName,
        convert(varchar(8000), tbls.name + '.' + tbl.name) as [Path]
    from 
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
    where
        tbls.name = @SchemaName
        and tbl.name = @TableName

    UNION ALL

    select
        ref.object_id as ObjectID,
        refs.name as SchemaName,
        ref.name as TableName,
        convert(varchar(8000), fks.[Path] + '/' + refs.name + '.' + ref.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables ref on fk.referenced_object_id = ref.object_id
        inner join sys.schemas refs on ref.schema_id = refs.schema_id
        inner join foreignkeys fks on fks.objectid = fk.parent_object_id
    where
        fks.objectid <> ref.object_id       
        AND fks.[Path] NOT LIKE '%' + refs.name + '.' + ref.name + '%'
),
parentsandchildren as
(
    select
        ObjectID,
        SchemaName,
        TableName
    from
        foreignkeys

    UNION ALL

    select 
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
        inner join parentsandchildren fks on fks.objectid = fk.referenced_object_id
    where
        not exists (select * from foreignkeys fk where fk.objectid = tbl.object_id)

)
select 
    SchemaName + '.' + TableName
from 
    parentsandchildren

UNION

select @SchemaName + '.' + @TableName;

If you run this script on T1, T2, or T3, it will return the list of all three tables (because it traverses the chain in both directions). Both of these scripts are also smart enough to not get caught up in self-joins and loops, should your table structure have either of those.

Running one of these two scripts would get you the list of tables that you could then select in the Generate Scripts menu. Just make sure to set the Type of data to scrip

StackExchange Database Administrators Q#29862, answer score: 2