/*
-----------------------------------------------------------------------------------------
This script was created when I experienced an issue with SQL 2005 and the 
IMPORT/EXPORT wizard which continually failed requiring manual intervention.
-----------------------------------------------------------------------------------------

Pre-STEPS
----------
I had to re-create the database, logins, schemas...by scripting it out.  The database
was originally restored with the wrong collation

SO I had to attempt to re-create the database by scripting it out with all the object 
level permissions, logins...ect.  

THEN IMPORT all the data to the new database tables...problem was the IMPORT wizard 
continued to fail with IDENTITY field errors even though the wizard was set to allow this...

So this script gets around that...

The script disables all triggers, then builds an INSERT INTO script
based on the SOURCE and DESTINATION databases.

In my example I renamed the current database something like DATABASE_OLD

then created the new database with the correct name

Ran the script to create the INSERT Statements
Ran the script which loaded all the tables

Verified the table counts
-----------------------------------------------------------------------------------------

*/
------------------------------------------
-- SQL 2005 Build Insert statement script
------------------------------------------
SET NOCOUNT ON

DECLARE @SQLCmd 	VARCHAR(MAX),
        @schemaid 	INT,
        @objectid 	INT,
        @is_identity	INT,
        @loopcnt 	INT,
        @DestinationDB 	SYSNAME,
        @SourceDB 	SYSNAME

---------------------------------------------------------
-- you need to enter the source and destination databases
-- and make sure you are in the source database when the 
-- script is run.
---------------------------------------------------------
SET 	@DestinationDB 	= ''
SET 	@SourceDB 	= ''
SET 	@loopcnt 	= 0

---------------------------------------------------------------------------------------
-- create script to DISABLE all triggers for all tables within the destination database
---------------------------------------------------------------------------------------

PRINT '-- Disable All Triggers'
PRINT '-----------------------'
SELECT	'USE ' 		+ @DestinationDB + CHAR(13) + CHAR(10) + 'GO'
SELECT 	'ALTER TABLE ' 	+ @DestinationDB + '.dbo.' + tables.name + ' DISABLE TRIGGER ALL ' + CHAR(13) + CHAR(10)
FROM	sys.schemas		schemas	(NOLOCK) 
	INNER JOIN sys.tables 	tables	(NOLOCK) on (schemas.schema_id = tables.schema_id)
WHERE	tables.type = 'U'

-------------------------------------------------------
-- get a list of all the tables, schema_id, & object_id
-------------------------------------------------------
SELECT	
	tables.schema_id,
	tables.name,
	tables.object_id,
	columns.is_identity
INTO
    	#InsertBuild
FROM
    	sys.tables 		tables	(NOLOCK)
	INNER JOIN sys.columns 	columns	(NOLOCK) on tables.object_id = columns.object_id
WHERE
    	schema_name(tables.schema_id) 	in ('dbo')	AND
	tables.type 			= 'U'
ORDER BY
	columns.is_identity 		DESC,
    	tables.schema_id,
	tables.name,
    	object_name(tables.object_id)

---------------------------------------------------------------------------------------------
-- select inital table to be scripted out and assign the values to the schema_id & object_id.
-- if the table has an identity field it will use the first sqlCmd which includes the 
-- IDENTITY_INSERT ON clause otherwise it will use a general INSERT statement.
---------------------------------------------------------------------------------------------
SELECT 	TOP 1 
	@schemaid 	= schema_id, 
	@objectid 	= object_id,
	@is_identity 	= is_identity
FROM 	#InsertBuild


WHILE 	(SELECT count(*) FROM #InsertBuild) > 0
BEGIN
------------------------------------------------
-- figure out if the table has an identity field
------------------------------------------------
    IF @is_identity = 1
     	BEGIN
			SELECT 	@SQLCmd =	'SET IDENTITY_INSERT ' 	+ @DestinationDB + '.dbo.' + tables.name + ' ON ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
    						'INSERT INTO ' 		+ @DestinationDB + '.dbo.' + tables.name + CHAR(10) + '		(' + CHAR(13) + CHAR(10)
			FROM
				sys.schemas		schemas 
				INNER JOIN sys.tables 	tables on (schemas.schema_id = tables.schema_id)
			WHERE
				schemas.schema_id 	= @schemaid 	and 
				tables.object_id 	= @objectid
	END
     ELSE	
     	BEGIN
			SELECT	@SQLCmd = 	'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + CHAR(10) + '		(' + CHAR(13) + CHAR(10)
			FROM
				sys.schemas		schemas 
				INNER JOIN sys.tables 	tables on (schemas.schema_id = tables.schema_id)
			WHERE
				schemas.schema_id 	= @schemaid 	and 
				tables.object_id 	= @objectid
		END
---------------------------------------------------------------------------------------------------------------------------------------
-- builds a list of all column names for the current table and append the values to the end of the column names to the INSERT statement
---------------------------------------------------------------------------------------------------------------------------------------
    SELECT	@SQLCmd	= @SQLCmd + '		[' + columns.name + '],' + CHAR(13) + CHAR(10)
    FROM	
    		sys.schemas		schemas (NOLOCK) 
     		INNER JOIN sys.tables 	tables 	(NOLOCK) on (schemas.schema_id 	= tables.schema_id)
	     	INNER JOIN sys.columns  columns (NOLOCK) on (tables.object_id 	= columns.object_id)
    WHERE	schemas.schema_id 	= @schemaid 	and 
		tables.object_id 	= @objectid
    ORDER BY
     		tables.name,
		columns.column_id

--------------------------------------------------------------
-- clips off the last comma at the end of the select statement
--------------------------------------------------------------
    SET 	@SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + CHAR(13) + '		)' + CHAR(13) + CHAR(10) +
               'SELECT' + CHAR(13) + CHAR(10)

---------------------------------------------------------------------------------------------------------------------------------------
-- builds a list of all column names for the current table and append the values to the end of the column names to the INSERT statement
---------------------------------------------------------------------------------------------------------------------------------------
    SELECT	@SQLCmd	= @SQLCmd + '		[' + columns.name + '],' + CHAR(13) + CHAR(10)
    FROM	sys.schemas		schemas	(NOLOCK)
		INNER JOIN sys.tables 	tables	(NOLOCK) on (schemas.schema_id = tables.schema_id)
     		INNER JOIN sys.columns	columns	(NOLOCK) on (tables.object_id = columns.object_id)
    WHERE
     		schemas.schema_id 	= @schemaid	and 
		tables.object_id 	= @objectid
    ORDER BY
     		tables.name,
		columns.column_id

--------------------------------------------------------------
-- clips off the last comma at the end of the select statement
--------------------------------------------------------------
    SET 	@SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + CHAR(13) + CHAR(10)

----------------------------------------------------------------------------------------------
-- identify the table name and turn off identity_insert option if table has an identity field
----------------------------------------------------------------------------------------------
IF @is_identity = 1
     	BEGIN
			SELECT 	@SQLCmd = 	@SQLCmd + 'FROM    ' 	+ @SourceDB 		+ '.dbo.' + tables.name +		CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +  
    						'SET IDENTITY_INSERT ' 	+ @DestinationDB 	+ '.dbo.' + tables.name + ' OFF ' + 	CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
    						'--------------------------------------------------------------'
		    	FROM	sys.schemas		schemas	(NOLOCK)
			        INNER JOIN sys.tables 	tables  (NOLOCK) on (schemas.schema_id = tables.schema_id)
		    	WHERE
			        schemas.schema_id 	= @schemaid	and 
				tables.object_id 	= @objectid
		END
     ELSE	
     	BEGIN
			SELECT 	@SQLCmd = 	@SQLCmd + 'FROM    ' 	+ @SourceDB 		+ '.dbo.' + tables.name + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +  
    						'--------------------------------------------------------------'
			FROM	sys.schemas		schemas	(NOLOCK)
			        INNER JOIN sys.tables 	tables 	(NOLOCK) on (schemas.schema_id = tables.schema_id)
			WHERE
				schemas.schema_id 	= @schemaid	and 
				tables.object_id 	= @objectid
		END

------------------------------------------------------------
-- print the statements that will be used to import the data
------------------------------------------------------------
    PRINT 	@SQLCmd
--  EXEC 	(@SQLCmd)


-- remove the current table from the temp table
-----------------------------------------------
	DELETE FROM 	#InsertBuild 
	WHERE 		schema_id = @schemaid 	and 
			object_id = @objectid

-- select the next table to be built
------------------------------------
	SELECT TOP 1 
		@schemaid 	= schema_id, 
		@objectid 	= object_id,
		@is_identity	= is_identity
	FROM 	#InsertBuild
END

PRINT '-- Enable All Triggers'
PRINT '----------------------'

-- create script to enable all triggers for all tables within the destination database
---------------------------------------------------------------------------------------
SELECT 	'ALTER TABLE ' + @DestinationDB + '.dbo.' + tables.name + ' ENABLE TRIGGER ALL '
FROM
	sys.schemas		schemas 
	INNER JOIN sys.tables 	tables on (schemas.schema_id = tables.schema_id)
WHERE	tables.type = 'U'

-- finished with the script, drop the temp table
------------------------------------------------
DROP TABLE #InsertBuild

SET NOCOUNT OFF