BCP and BULK load data for all tables in a given DB

January 30, 2008 | Leave a Comment

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’

 

Posted by | Filed Under SQL 

Recently Ran Queries

January 23, 2008 | Leave a Comment

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

 

Posted by | Filed Under SQL