intellectually constipated

patrick gallucci's technology brain drain

Generate BCP, Bulk Insert code with TSQL

Generate BCP, Bulk Insert code with TSQL

Rate This
  • Comments 5

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
  • PingBack from http://msdnrss.thecoderblogs.com/2007/08/10/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

  • I thought that openrowset would not accept a variable parameter

  • Wow wow wow wow I love it!!!

    Thank you very much!

  • 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?

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post