Sql-server – SQL Server 2008: Diff table data on different servers with exact schema

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 script option to Schema and Data, or else you'll only get the table structure.

EDIT

I don't know of any tool which will script out individual records the way you're asking for them. Even writing this would be difficult at first glance, because you would need to know which records you need to insert, all records which depend upon that, all records which depend upon those, etc. That's a fairly straightforward recursion problem by itself. But then you'd need to maintain a structure telling when each row gets inserted so you don't double-insert records. For an arbitrary table structure (especially one with loops), this becomes a challenge.

But based strictly on your example, I'm not sure if there's actually any value to doing what you want regarding specialized insertion. As far as SQL Server is concerned, there's no difference between the two scenarios:

Scenario A:

1. Insert Bob's Person record
2. Insert Animals related to Bob
3. Insert Fred's Person record
4. Insert Animals related to Fred

Scenario B:

1. Insert Bob and Fred's Person records
2. Insert all Animal records related to Bob and Fred

After the insertions are complete, all select statements will return the same results regardless of which path you choose. Even if you have a trigger or some other complex logic that depends upon inserting in a particular order, I'd have a hard time seeing a case in which A works but B doesn't. I could see this making sense if we were discussing a 1980s RDBMS and you used interleaving parent and child records in a file to improve performance, but that's not really applicable here.

What you might look at instead is getting the order of insertions correct: insert into reference data tables first and go on down the chain. In your example, that's inserting into Person and then into Animal. That way, you won't have any foreign key constraint violations and all records would be up to date. Or, using a tool like Atlantis Interactive's Data Inspector, you can have it disable foreign keys during the load and then re-enable them afterward.

Yes, stay away from the sp_OA* OLE Automation procedures.

If you want / need to handle this purely within SQL Server then you can do so using SQLCLR, which replaces the sp_OA* OLE Automation procedures. And if you aren't using xp_cmdshell for anything else, then no need to enable it just for this (although to be fair, if using SQL Agent, then a CMD step would be able to execute BCP and/or 7zip without the need for enabling xp_cmdshell).

With SQLCLR you can:

• do the export from within T-SQL as well and not need to mess with BCP which is an external process (see FileStream).
• zip the file, as a separate step, since. NET has methods to gzip and gunzip (see GZipStream).

• encrypt with a password (see RijndaelManaged and AesManaged). While you did specify AES, the MSDN documentation for AesManaged states:

  The AES algorithm is essentially the Rijndael symmetric algorithm with a fixed block size and iteration count. This class functions the same way as the RijndaelManaged class but limits blocks to 128 bits and does not allow feedback modes.

  The difference is if you are using SQL Server 2005, 2008, or 2008 R2. Those versions are limited to using .NET 3.5, and the MSDN documentation for AesManaged (for that version) states that it has a Host Protection Attribute (HPA) which means that any assembly using that class has to be marked as PERMISSION_SET = UNSAFE, whereas RijndaelManaged does not have that issue and can be used in an EXTERNAL_ACCESS assembly. However, it seems that the documentation for AesManaged shows that, starting in .NET Framework version 4.0 (SQL Server 2012, 2014, and 2016 use .NET Framework 4.0 and newer--currently up to 4.6), the note about the HPA has been removed. Even if UNSAFE is acceptable, please note that the specific HPA is MayLeakOnAbort, which means "might leak memory if the operation is terminated".

In this setup, the password is part of the encryption, not the compression. Hence you either have a zip file containing an encrypted file, or you have an encrypted file containing the zip file. The former method gives you a file that can be unzipped by anyone but then only read by someone with the correct password. The latter method requires knowing the password to get the compressed file, and also allows for better compression since the compression will be done on a text file (with a greater likelihood of compressible patterns).

While I have not implemented the encryption algorithms yet, I have coded both the file export (of any random query) and GZip/GZunzip in SQLCLR and can state that they do run efficiently, or at least can be made to be efficient (and can also be made to be inefficient). By this I mean I have used them to extract queries producing 5 GB text files, and GZipping those 5 GB text files, and memory was never adversely affected since both operations are streamed. Please note that the .NET-provided GZipStream class does not support the Zip64 format, and so is limited to 4 GB of source data.

For anyone who is interested in pre-built SQLCLR functions to do the extract and compression (the ones I mentioned above), they are available in the SQL# library (which I am the author of). Please note that:

• while there is a Free version of SQL#, the two objects mentioned here, DB_BulkExport and File_GZip, are only available in the Full version.
• in order to overcome the 4 GB limitation of the GZipStream class, I had to incorporate a 3rd-party library that does support Zip64.
• soon enough there will be functions to handle file encryption and decryption via Rijndael (available for SQL Server 2005 - 2016 at least) and Aes (available for SQL Server 2012 - 2016 at least).