Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to pull a partial dataset from production for test environments - e.g. Copy the production database with only the first 100 rows of each table or 5% of the data - which ever comes first. Simple enough. For each table, just... SELECT TOP 100 * INTO DESTINATION FROM SOURCE"

The problem is maintaining data integrity for FK. Is there a native SQL Server SP/FX to verify the integrity of each row's FK after the data has been copied over?

If not, once the production schema is copied to sandbox environments, I was planning to implement the following logic:

PSUEDO:

For each source table{
 While (Destination Tbl =<100 rows/5% of Source Tbl COUNT(*)) {
   If FK exists on source tbl{
     For each FK, query referenced FK tbl and insert results into FK's corresponding destination tbl
   }
  Insert row from source tbl to destination tbl
 }
}

I don't want to recreate the wheel or make it harder than it needs to be. If you see any holes in my logic, please let me know. Thanks in advance.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

I think you are over complicating your requirement.

Your best option is to

  1. Create a backup of PROD on a test server
  2. drop all the FK's
  3. migrate the data you want to destination server using SSIS or T-SQL or BCP (which ever one you prefer).
  4. create FK's back.

In SQL Server you don't have a tool to do it out of the box. You will end up writing more complex tsql code which I doubt will achieve what you are trying to do + you have to take care of Identity columns as well.

Below script would loop over all the tables and select them one-by-one by taking into account referential integrity constraints (i.e. foreign keys).

Note: Below script is borrowed and modified from here written by Jamie Thomson.

    -- make sure to use SQLCMD mode in SSMS and change the database Name.
    :setvar db Adventureworks2008


    DECLARE @tables TABLE (
        schemaName sysname
    ,   tableName sysname
    ,   level int
    );

    with fk_tables as (
        select  s1.name as from_schema
        ,       o1.Name as from_table
        ,       s2.name as to_schema
        ,       o2.Name as to_table
        from    $(db).sys.foreign_keys fk
        inner   join $(db).sys.objects o1
        on      fk.parent_object_id = o1.object_id
        inner   join $(db).sys.schemas s1
        on      o1.schema_id = s1.schema_id
        inner   join $(db).sys.objects o2
        on      fk.referenced_object_id = o2.object_id
        inner   join $(db).sys.schemas s2
        on      o2.schema_id = s2.schema_id
        --For the purposes of finding dependency hierarchy 
        --we're not worried about self-referencing tables
        where   not (   s1.name = s2.name 
                    and o1.name = o2.name)
    )

    ,ordered_tables AS (
        SELECT  s.name as schemaName, t.name as tableName, 0 AS Level
        FROM    (   select  *
                    from    $(db).sys.tables 
                    where   name <> 'sysdiagrams') t
        INNER   JOIN $(db).sys.schemas s
        on      t.schema_id = s.schema_id
        LEFT    OUTER JOIN fk_tables fk
        ON      s.name = fk.from_schema
        AND     t.name = fk.from_table
        WHERE   fk.from_schema IS NULL
        UNION   ALL
        SELECT  fk.from_schema, fk.from_table, ot.Level + 1
        FROM    fk_tables fk
        INNER   JOIN ordered_tables ot
        ON      fk.to_schema = ot.schemaName
        AND     fk.to_table = ot.tableName
    )
    insert  @tables
    select  distinct ot.schemaName,ot.tableName,ot.Level
    from    ordered_tables ot
    inner   join (
            select  schemaName,tableName,MAX(Level) maxLevel
            from    ordered_tables
            group   by schemaName,tableName
    ) mx
    on ot.schemaName = mx.schemaName
    and ot.tableName = mx.tableName
    and mx.maxLevel = ot.Level
    ORDER   BY [Level] DESC;

    DECLARE tables_cursor CURSOR FORWARD_ONLY
    FOR
    SELECT  schemaName, tableName
    FROM    @tables
    ORDER   BY [Level] DESC;

    DECLARE @schemaName         sysname
    ,       @tableName          sysname
    ,       @schemaTableName    NVARCHAR(MAX);

    DECLARE @vSQL nvarchar(max);
    OPEN    tables_cursor;
    FETCH   NEXT FROM tables_cursor INTO @schemaName, @tableName

    BEGIN TRAN
    BEGIN TRY
        WHILE   @@FETCH_STATUS = 0
        BEGIN
                SET @schemaTableName = '[' + @schemaName + '].[' + @tableName + ']';
                SET @vSQL = 'select top(100) * FROM $(db).' + @schemaTableName;
                exec (@vSQL);
                PRINT 'Selected ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows from $(db).' + @schemaTableName;
                FETCH NEXT FROM tables_cursor INTO @schemaName, @tableName;
        END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        PRINT ERROR_MESSAGE();
    END CATCH

    IF @@TRANCOUNT > 0
        COMMIT TRAN;

    IF CURSOR_STATUS('global','tables_cursor') >= 0
    BEGIN
        CLOSE tables_cursor;
        DEALLOCATE tables_cursor;
    END
    GO
share|improve this answer
    
Thank you, Kin! The linked article is essentially spot on to my dilemma. –  SRTR4K Sep 2 '13 at 7:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.