Generate BCP, Bulk Insert code with TSQL

This script will generate tsql code to bcp and bulk load data for all tables in a given DB.

 SET NOCOUNT ON

GO

 DECLARE @path nvarchar(2000), @batchsize nvarchar(40),

         @format nvarchar(40), @serverinstance nvarchar(200),

         @security nvarchar(800)

 SET @path = 'C:\Temp\';

 SET @batchsize = '1000000' -- COMMIT EVERY n RECORDS

 SET @serverinstance = 'PGALLUCC-M7' --SQL Server \ Instance name

 SET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword

 --GENERATE CONSTRAINT NO CHECK

 PRINT '--NO CHECK CONSTRAINTS'

 SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT '

 +  QUOTENAME( CONSTRAINT_NAME )

 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

 --DISABLE TRIGGERS

 PRINT '--DISABLE TRIGGERS'

 SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 --BCP-OUT TABLES

 PRINT '--BCP OUT TABLES '

 SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA )

 + '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"'

 + @batchsize + '" -e"' + @path + '' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + ''

 + TABLE_NAME + '.out"  -S"' + @serverinstance + '" ' + @security + ''

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 -- CREATE NON-XML FORMAT FILE

 PRINT '--NON-XML FORMAT FILE'

 SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.'

 + QUOTENAME( TABLE_NAME ) + '" format nul -n -CRAW -f "' + @path + ''

 + TABLE_NAME + '.fmt"  --S"' + @serverinstance + '" ' + @security + ''

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 -- CREATE XML FORMAT FILE

 PRINT '--XML FORMAT FILE'

 SELECT  'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA )

 + '.' + QUOTENAME( TABLE_NAME ) + '" format nul -x -n -CRAW -f "'

 + @path + '' + TABLE_NAME + '.xml"  -S"' + @serverinstance + '" ' + @security + ''

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 --TRUNCATE TABLE

 PRINT '--TRUNCATE TABLE'

 SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + '

 GO '

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 --BULK INSERT

 PRINT '--BULK INSERT'

 SELECT DISTINCT 'BULK INSERT ' + QUOTENAME(TABLE_CATALOG) + '.'

 + QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + '

    FROM ''' + @path + '' + TABLE_NAME + '.Dat''

    WITH (FORMATFILE = ''' + @path + '' + TABLE_NAME + '.FMT'',

          BATCHSIZE = ' + @batchsize + ',

          ERRORFILE = ''' + @path + 'BI_' + TABLE_NAME + '.ERR'',

          TABLOCK);

 GO '

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 --OPENROWSET

 PRINT '--OPENROWSET'

 SELECT DISTINCT 'INSERT INTO ' + QUOTENAME(TABLE_CATALOG) + '.'

 + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '

     SELECT *

       FROM  OPENROWSET(BULK  ''' + @path + '' + TABLE_NAME + '.Dat'',

       FORMATFILE=''' + @path + '' + TABLE_NAME + '.Xml''

       ) as t1 ;

 GO '

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 --GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD

 PRINT '--CHECK CONSTRAINT'

 SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT '

 +  QUOTENAME( CONSTRAINT_NAME )

 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

 --ENABLE TRIGGERS

 PRINT '--ENABLE TRIGGERS'

 SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'

 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Comments

Anonymous
August 10, 2007
PingBack from http://msdnrss.thecoderblogs.com/2007/08/10/generate-bcp-bulk-insert-code-with-tsql/
Anonymous
August 10, 2007
This script will generate tsql code to bcp and bulk load data for all tables in a given DB. SET NOCOUNT
Anonymous
November 19, 2007
I thought that openrowset would not accept a variable parameter
Anonymous
May 22, 2008
Wow wow wow wow I love it!!! Thank you very much!
Anonymous
November 19, 2008
I have been using sql since the dawn of sql. And I still am not clear on creating bcp format files. I have to do them by hand. The utility seems ok, but no thanks. All I want is something that says: declare @servername nvarchar(50) declare @databasename nvarchar(50) declare @tablename nvarchar(50) declare @filename nvarchar(50) declare @connectiontype nvarchar(50) doit (bpc ---- @variables above. Why can't this be done?