Generate BCP, Bulk Insert code with TSQL

  • Article

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'

Technorati Tags: bcp, bulk insert, openrowset, information_schema

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?