This will be useful while migrating the data from one server to another
SET NOCOUNT ONGOSELECT @@SERVERNAMEDECLARE @path nvarchar(2000), @batchsize nvarchar(40),@format nvarchar(40), @serverinstance nvarchar(200),@security nvarchar(800)SET @path = 'C:\Temp\';SET @batchsize = '100000' -- COMMIT EVERY n RECORDSSET @serverinstance = @@SERVERNAME --SQL Server \ Instance nameSET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword--GENERATE CONSTRAINT NO CHECKPRINT '--NO CHECK CONSTRAINTS'SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT '+ QUOTENAME( CONSTRAINT_NAME )FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS--DISABLE TRIGGERSPRINT '--DISABLE TRIGGERS'SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'--BCP-OUT TABLESPRINT '--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 FILEPRINT '--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 FILEPRINT '--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 TABLEPRINT '--TRUNCATE TABLE'SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + 'GO 'FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'--BULK INSERTPRINT '--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'--OPENROWSETPRINT '--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 LOADPRINT '--CHECK CONSTRAINT'SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT '+ QUOTENAME( CONSTRAINT_NAME )FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS--ENABLE TRIGGERSPRINT '--ENABLE TRIGGERS'SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Post a Comment
Post a Comment