Script 1 - Populate tables.

(all colour markup done by www.markitup.com)

/*******************************************************************************

Name:                           DataImportScript

Author:                        M.Pearson
Creation Date:                
Version:                1.0

Program Overview:
This script is useful when duplicating a database. It works when the databases
are called <DatabaseName> and <DatabaseName_Old>

Press Ctrl+U to select the <DatabaseName>, and run the script on that database.
Copy the results into a new window and run that to copy the data from the 
<DatabaseName_Old> database to the new <DatabaseName>


This script will also ensure that
- triggers are disabled on all tables during the copy process
- tables are copied in the correct order to avoid breaking FK Constraints
- identity Inserts are enabled where applicable



Modification History:
-------------------------------------------------------------------------------
Version Date                Name                Modification
-------------------------------------------------------------------------------
1.0                         M.Pearson        Initial Creation


*******************************************************************************/


SET NOCOUNT ON

DECLARE
        @vstrSource varchar(100),
        @vstrDestination varchar(100),
        @intCounter int,
        @intRowCount int,
        @strTableName varchar(100),
        @strIdentityField char(1),
        @strIdentitySQL nvarchar(100),
        @strSQL varchar(8000),
        @strColumnName varchar(100),
        @strColumnList varchar(4000),
        @strCheckSum varchar(200),
        @strLog varchar(100),
        @intRowsInserted int

-- Set the variables
        SELECT @vstrSource = DB_Name() + '_Old'
        SELECT @vstrDestination = DB_Name()
        SELECT @intCounter = 1
        SELECT @intRowCount = 1

-- =====================================================
-- Creeate the temporary tables
-- =====================================================

CREATE TABLE #Hierarchy
        (
        Hierarchy int,
        Child varchar(100),
        Parent varchar(100)
        )

CREATE TABLE #TableInfo
        (
        TableName varchar(100),
        Hierarchy int,
        IdentityField char(1),
        CheckSumSource int,
        CheckSumDestination int
        )
        


-- =====================================================
-- Find the hierarchies and populate #TableInfo
-- =====================================================
        
        INSERT INTO #Hierarchy
        SELECT DISTINCT 1 AS 'Hierarchy', S1.name AS 'Child', SO.Name AS 'Parent'
        FROM dbo.sysforeignkeys FK
        INNER JOIN dbo.sysobjects SO
                ON FK.rkeyID = SO.id
        INNER JOIN dbo.sysobjects S1
                ON FK.fkeyID = S1.id
        WHERE FK.rkeyID <> FK.fkeyID         -- to exclude self-related tables, otherwise next loop would never finish 
        
        
        WHILE @intRowCount <> 0
        BEGIN
                UPDATE #Hierarchy
                SET Hierarchy = Hierarchy + 1
                WHERE Hierarchy = @intCounter
                        AND Parent IN  (SELECT DISTINCT Child
                                        FROM #Hierarchy 
                                        WHERE Hierarchy = @intCounter)
        
                SET @intRowCount = @@Rowcount
        
                SELECT @intCounter = @intCounter + 1
        END
        
         
        -- Add the tables that have no Foreign-Key relationships...
        INSERT INTO #Hierarchy
        SELECT -1, [name], ' - '
        FROM dbo.sysobjects
        WHERE [name] NOT IN (SELECT DISTINCT Parent FROM #Hierarchy)
                AND [Name] NOT IN (SELECT DISTINCT Child FROM #Hierarchy)
                AND xtype = 'U'
        
        
        -- Add the tables that are Parents only
        INSERT INTO #Hierarchy
        SELECT DISTINCT 0, Parent, ' - '
        FROM #Hierarchy
        WHERE Parent NOT IN (SELECT Child FROM #Hierarchy)
                AND Hierarchy <> -1
        
        -- Add 1 to adjust the hierarchies to start at 0
        UPDATE #Hierarchy
        SET Hierarchy = Hierarchy + 1
        
        
        -- Insert this into the #TableInfo
        INSERT INTO #TableInfo (TableName, Hierarchy, IdentityField)
        SELECT DISTINCT Child, Hierarchy, 'N'
        FROM #Hierarchy
        ORDER BY Hierarchy, Child
        
        -- Clean up
        -- DROP TABLE #Hierarchy        


-- =====================================================
-- Identify tables that will require identity insert...
-- =====================================================

        UPDATE #TableInfo
        SET IdentityField = 'Y'
        FROM dbo.sysobjects 
        INNER JOIN dbo.syscolumns 
                ON dbo.sysobjects.id = dbo.syscolumns.id 
        WHERE dbo.syscolumns.status = 0x80
                AND dbo.sysobjects.name = #TableInfo.TableName

-- =====================================================
-- Disable any triggers 
-- =====================================================

PRINT 'sp_msforeachtable ''ALTER TABLE ? DISABLE TRIGGER  all'''
PRINT 'GO'
PRINT ''



-- =====================================================
-- Open cursor - work through tables importing data
-- =====================================================

DECLARE rstTables CURSOR FOR
        SELECT TableName, IdentityField
        FROM (SELECT TableName, IdentityField, Max(Hierarchy) as mx
        FROM #TableInfo
        GROUP BY TableName, IdentityField) q1
        ORDER BY mx

OPEN rstTables
FETCH NEXT FROM rstTables INTO @strTableName, @strIdentityField 
WHILE @@Fetch_Status = 0-- <> -1
BEGIN
        

        -- Is IDENTITY_INSERT required?
        IF @strIdentityField = 'Y'
        BEGIN
                SET @strIdentitySQL = 'SET IDENTITY_INSERT ' + @vstrDestination + '.dbo.[' + @strTableName + '] ON'
                PRINT @strIdentitySQL


                PRINT 'GO'
                PRINT ''

        END

        
                -- ----------------------------------------
                -- Open a cursor to get the fields from the 
                -- table to build up the insert statement
                -- ----------------------------------------
                PRINT 'PRINT ''' + @strTableName + ''''

                DECLARE rstColumns CURSOR FOR
                        SELECT dbo.syscolumns.name AS [Column]
                        FROM dbo.sysobjects 
                        INNER JOIN dbo.syscolumns 
                                ON dbo.sysobjects.id = dbo.syscolumns.id 
                        INNER JOIN dbo.systypes
                                ON dbo.syscolumns.xusertype = dbo.systypes.xusertype
                        WHERE (dbo.sysobjects.xtype = 'u') 
                                AND (dbo.sysobjects.name = @strTableName)
                        ORDER BY dbo.syscolumns.colid
                 
                SET @strColumnList = ''
                
                OPEN rstColumns
                        FETCH NEXT FROM rstColumns INTO @strColumnName
                        WHILE @@Fetch_Status = 0-- <> -1
                        BEGIN
                                
                                SET @strColumnList = @strColumnList + '[' + @strColumnName + '],'
                
                        FETCH NEXT FROM rstColumns INTO @strColumnName
                          END
                 
                CLOSE rstColumns
                DEALLOCATE rstColumns
                 
                
                SET @strColumnList = left(@strColumnList,len(@strColumnList)-1)
                
                -- Put the string together and execute it...
                SET @strSQL =  'INSERT INTO ' 
                                + @vstrDestination + '.dbo.[' + @strTableName +']'
                                + ' (' + @strColumnList +')' + CHAR(13)
                                + ' SELECT ' + @strColumnList + CHAR(13)
                                + ' FROM ' 
                                + @vstrSource + '.dbo.[' + @strTableName +']'

                PRINT @strSQL

                 SET @intRowsInserted = @@RowCount
        -- ---> End of "sub-cursor"  <---
        
        PRINT 'GO'
        PRINT ''

        -- If IDENTITY_INSERT was required - switch it off again
        IF @strIdentityField = 'Y'
        BEGIN
                SET @strIdentitySQL = 'SET IDENTITY_INSERT ' + @vstrDestination + '.dbo.[' + @strTableName + '] OFF'
                PRINT @strIdentitySQL

                PRINT 'GO'
                PRINT ''
        END



FETCH NEXT FROM rstTables INTO @strTableName, @strIdentityField 
END

CLOSE rstTables
DEALLOCATE rstTables




-- =====================================================
-- Re-enable any triggers 
-- =====================================================

PRINT 'sp_msforeachtable ''ALTER TABLE ? ENABLE TRIGGER  all'''
PRINT 'GO'
PRINT ''



-- =====================================================
-- Clean Up
-- =====================================================
DROP TABLE #Hierarchy
DROP TABLE #TableInfo



GO