intellectually constipated : Generate BCP, Bulk Insert code with TSQL

intellectually constipated

patrick gallucci's sql server brain drain

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'

Published Friday, August 10, 2007 10:48 PM by Patrick Gallucci

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

	MSDN Blog Postings » Generate BCP, Bulk Insert code with TSQL said: PingBack from http://msdnrss.thecoderblogs.com/2007/08/10/generate-bcp-bulk-insert-code-with-tsql/ August 10, 2007 11:25 PM
	Noticias externas said: This script will generate tsql code to bcp and bulk load data for all tables in a given DB. SET NOCOUNT August 10, 2007 11:35 PM
	SPierce said: I thought that openrowset would not accept a variable parameter November 19, 2007 3:09 PM
	yosiasz said: Wow wow wow wow I love it!!! Thank you very much! May 22, 2008 12:58 PM
	foxjazz said: 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? November 19, 2008 12:35 PM

breathing air