TSql – Bulk Copy Database Data to Another Database

Posted: 2012-12-07 in Sql
Tags: ,

Copies data from one database to another.
Assumes the tables schema are the same. Use the Management studio Generate scripts to create the tables, constraints, stats, and indexes.

The script is deliberately written in 3 parts to help ease of customization

Handles IDENTITY INSERT tables and constraints (although uses simple brute for with max of x attempts which isn’t so elegant)

Simply run tsql script in the destination database and change the @CopyDatabaseTables to set the SOURCE database for the data.
If you need to restrict it down a bit (control the tables) edit the ‘%someRestriction%’ statement.


-- chocosmith.wordpress.com
 -- create bulk copy tables scripts from one database to another
 -- does not create tables
 --- part 1 create the working table set
 --- part 1 create the working table set
 --- part 1 create the working table set

DECLARE @CopyDatabaseTables varchar(50)
 SET @CopyDatabaseTables = 'destinationDatabase.dbo.'
 --create temp table
 IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#tempIdentityTables'))
 DROP TABLE #tempIdentityTables
 CREATE TABLE #tempIdentityTables(
 [TABLE_NAME] [varchar](255) NOT NULL,
 tableColumns [varchar](1000) not null,
 command VARCHAR(MAX),
 numberOfIdentityColumns int default (0),
 processed int default (0),
 )
 insert into #tempIdentityTables ([TABLE_NAME], tableColumns, numberOfIdentityColumns)
 SELECT DISTINCT TABLE_NAME
 ,( SELECT stuff( (
 SELECT ',' + '[' + P.COLUMN_NAME + ']'
 FROM INFORMATION_SCHEMA.COLUMNS AS P
 WHERE P.TABLE_NAME = PM.TABLE_NAME
 ORDER BY P.TABLE_NAME
 FOR XML PATH('')
 ) , 1, 1, '')
 ) as tableColumns
 , (select SUM(COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) as numberOfIdentityColumns from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = PM.TABLE_NAME group by TABLE_NAME ) as numberOfIdentityColumns
 FROM INFORMATION_SCHEMA.COLUMNS as PM
 -- WHERE TABLE_NAME like '%someRestriction%'
 --- part 2 create the sql scripts ready for EXEC
 --- part 2 create the sql scripts ready for EXEC
 --- part 2 create the sql scripts ready for EXEC

-- create the exec commands to do the copy jobs
 DECLARE @name VARCHAR(255)
 DECLARE @tableColumns [varchar](1000)
 DECLARE @numberOfIdentityColumns int
 DECLARE @SQL VARCHAR(MAX)
 select top 1 @name = [TABLE_NAME] , @tableColumns = tableColumns, @numberOfIdentityColumns = numberOfIdentityColumns from #tempIdentityTables where processed = 0

WHILE @name IS NOT NULL
 BEGIN
 set @SQL = ''

if (@numberOfIdentityColumns > 0)
 SELECT @SQL = 'SET IDENTITY_INSERT [' + RTRIM(@name) +']' + ' ON ' + CHAR(13)+CHAR(10)

SELECT @SQL = @SQL + 'insert into [' + RTRIM(@name) +']' +'(' + @tableColumns + ')' + CHAR(13)+CHAR(10)
 SELECT @SQL = @SQL + 'select * from '+@CopyDatabaseTables + '[' + RTRIM(@name) +']' + CHAR(13)+CHAR(10)

if (@numberOfIdentityColumns > 0)
 SELECT @SQL = @SQL + 'SET IDENTITY_INSERT [' + RTRIM(@name) +']' + ' OFF' + CHAR(13)+CHAR(10)

update #tempIdentityTables
 set command = @SQL, processed = 1
 where #tempIdentityTables.TABLE_NAME = @name

--if no select the name would be last value and we'd get into an endless loop.... grrr
 set @name = null;
 select top 1 @name = [TABLE_NAME] , @tableColumns = tableColumns, @numberOfIdentityColumns = numberOfIdentityColumns from #tempIdentityTables where processed = 0

END

-- part 3 the execute of the previously generated scripts, tweak as needed.
 -- part 3 the execute of the previously generated scripts, tweak as needed.
 -- part 3 the execute of the previously generated scripts, tweak as needed.
 -- hit rerun here if you get stuck on a constraint
 -- run the scripts, this will fail on constraints but we mark them as -1. You can slowly get them all if you keep running this code down till all have gone through
 DECLARE @ExecSQL VARCHAR(MAX)
 DECLARE @tableName VARCHAR(255) = null
 DECLARE @retryCount int = 10 --handles constraints, hopefully within 10 try you have them covered

select top 1 @tableName = [TABLE_NAME] , @ExecSQL = command from #tempIdentityTables where processed = 0 OR processed = -1 OR processed = 1
 While @retryCount > 0
 BEGIN

WHILE @tableName IS NOT NULL
 BEGIN

BEGIN TRY
 print 'executing.... ' + @ExecSQL
 EXEC (@ExecSQL)

update #tempIdentityTables
 set processed = 2
 where #tempIdentityTables.TABLE_NAME = @tableName
 END TRY
 BEGIN CATCH
 print 'Error detected: '
 print ERROR_MESSAGE()
 update #tempIdentityTables
 set processed = -2
 where #tempIdentityTables.TABLE_NAME = @tableName
 END CATCH;

--if no select the name would be last value and we'd get into an endless loop.... grrr
 set @tableName = null;
 select top 1 @tableName = [TABLE_NAME] , @ExecSQL = command from #tempIdentityTables where processed = 0 OR processed = -1 OR processed = 1

END

update #tempIdentityTables
 set processed = -1
 where processed = -2

set @retryCount = @retryCount - 1;

select top 1 @tableName = [TABLE_NAME] , @ExecSQL = command from #tempIdentityTables where processed = 0 OR processed = -1 OR processed = 1

END
 Go

-- select * from #tempIdentityTables where processed = 0 OR processed = -1 OR processed = 1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s