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'

Technorati Tags: bcp, bulk insert, openrowset, information_schema