InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 07:30:07 GMT20http://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxThis article should help [b][url]http://www.sqlservercentral.com/articles/T-SQL/67898/[/url][/b].Tue, 13 Nov 2012 18:24:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxDelete rows in batches and don't forget to rebuild the indexes after deletes based on fragmentation percentage.Tue, 13 Nov 2012 13:33:52 GMTmuthyala_51http://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxI can't use truncate, I need to delet over 5 mil records and leave about 2milTue, 13 Nov 2012 11:22:05 GMTKrasavitahttp://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxBCP will pull data from a file. This method is to pull data from an existing table to another table.Could also use SQLBulkCopy .net library (or SSIS).Tue, 07 Aug 2012 22:08:48 GMTJohnnyDBAhttp://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxTruncate and insert????? then why we cant go for BCP? Will it not be faster?Tue, 07 Aug 2012 21:29:25 GMTSQL Showhttp://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxI am doing something similar. We need to get data from one server to the other with minimal impact to the developers deployment process and minimal bandwidth. What we opted for was a local bulk copy into another database then compress and backup / restore to the other server. Overall it was fastest compared to other methods.Here is what I have so far. This is the part that runs on the source server. You could modify it to however you see fit (maybe restore as a different name then do a name swap at the database level). I don't have the other half completed yet.Good luck[code="sql"]DECLARE -- configurable variables @sourceDatabase SYSNAME = '' -- source database, @dataPath VARCHAR(255) = '' -- path to data file, @logPath VARCHAR(255) = '' -- path to log file, @tableListFile VARCHAR(255) = '' -- text file containing the list of tables to copy, @destinationDataSize INT = 500 -- destination database size in MB, @destinationLogSize INT = 50 -- destination log file size in MB -- non-configurable variables, @destinationDatabase SYSNAME, @TSQL VARCHAR(MAX) -- table specific variables, @tableName SYSNAME, @columnList VARCHAR(MAX)-- initializationsSELECT @destinationDatabase = 'Copy_' + @sourceDatabase, @dataPath = CASE WHEN RIGHT(@dataPath, 1) <> '\' THEN @dataPath + '\' ELSE @dataPath END, @logPath = CASE WHEN RIGHT(@logPath, 1) <> '\' THEN @logPath + '\' ELSE @logPath ENDIF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL DROP TABLE ##t_TableListCREATE TABLE ##t_TableList ( TableName SYSNAME NOT NULL)IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL DROP TABLE ##t_ColumnListCREATE TABLE ##t_ColumnList ( ColumnList VARCHAR(MAX) NOT NULL)SET NOCOUNT ONBEGIN TRY -- Cleanup SELECT @TSQL = '' + 'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) ' + 'BEGIN ' + 'ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + 'USE [master] DROP DATABASE [' + @destinationDatabase + '] ' + 'END' PRINT(@TSQL) EXEC(@TSQL) -- Create Database SELECT @TSQL = '' + 'CREATE DATABASE [' + @destinationDatabase + '] ' + 'ON PRIMARY ' + '( NAME = N''' + @destinationDatabase + '_data'', FILENAME = N''' + @dataPath + @destinationDatabase + '_data.mdf'' , SIZE = ' + CONVERT(VARCHAR, @destinationDataSize) + 'MB , FILEGROWTH = 512000KB ) ' + 'LOG ON ' + '( NAME = N''' + @destinationDatabase + '_log'', FILENAME = N''' + @logPath + @destinationDatabase + '_log.ldf'' , SIZE = ' + CONVERT(VARCHAR, @destinationLogSize) + 'MB , FILEGROWTH = 10%)' PRINT(@TSQL) EXEC(@TSQL) -- Get Table List from File SELECT @TSQL = '' + 'BULK INSERT ##t_TableList FROM ''' + @tableListFile + ''' with (FirstRow = 1)' EXEC(@TSQL)-- DEBUG SELECT * FROM ##t_TableList -- Process Each Table WHILE EXISTS ( SELECT 1 FROM ##t_TableList ) BEGIN SELECT TOP 1 @tableName = tableName, @columnList = '' FROM ##t_TableList DELETE FROM ##t_TableList WHERE tableName = @tableName PRINT(@tableName) -- Fetch Column List BEGIN SELECT @TSQL = 'USE [' + @sourceDatabase + '] DECLARE @columnList VARCHAR(MAX) = '''' SELECT @columnList = @columnList + CASE WHEN @columnList = '''' THEN ''['' + [name] + '']'' ELSE '',['' + [name] + '']'' END FROM sys.columns c WHERE c.is_computed = 0 AND OBJECT_SCHEMA_NAME([object_id]) = ''dbo'' AND OBJECT_NAME([object_id]) = ''' + @tableName + ''' ORDER BY column_id SELECT @columnList' INSERT INTO ##t_ColumnList (ColumnList) EXEC(@TSQL) SELECT @columnList = ColumnList FROM ##t_ColumnList DELETE FROM ##t_ColumnList-- PRINT(@columnList) END -- Generate Destination Schema -- **note** Must create dummy join so identity columns are not propagated to destination database SELECT @TSQL = 'SELECT TOP 0 ' + @columnList + ' INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] LEFT JOIN ( SELECT TOP 1 ''Dummy'' AS DummyColumn FROM master.sys.databases ) b ON b.[DummyColumn] = ''Dummy''' PRINT(@TSQL) EXEC(@TSQL) -- Insert Data SELECT @TSQL = 'INSERT INTO [' + @destinationDatabase + '].[dbo].[' + @tableName + '] WITH (TABLOCK) (' + @columnList + ') SELECT ' + @columnList + ' FROM [' + @sourceDatabase + '].[dbo].[' + @tableName + '] WITH (NOLOCK)' PRINT(@TSQL) EXEC(@TSQL) -- Enable Table Compression SELECT @TSQL = 'USE [' + @destinationDatabase + '] ALTER TABLE [dbo].[' + @tableName + '] REBUILD WITH (DATA_COMPRESSION=PAGE)' PRINT(@TSQL) EXEC(@TSQL) ENDEND TRYBEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); IF @@TRANCOUNT > 0 ROLLBACK TRAN ELSE -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. );END CATCH-- CleanupSELECT @TSQL = 'IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = ''' + @destinationDatabase + ''' ) USE [' + @destinationDatabase + '] ALTER DATABASE [' + @destinationDatabase + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE [master] DROP DATABASE [' + @destinationDatabase + ']'PRINT(@TSQL)EXEC(@TSQL)IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL DROP TABLE ##t_TableListIF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL DROP TABLE ##t_ColumnList[/code]Tue, 07 Aug 2012 10:06:38 GMTJohnnyDBAhttp://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxif you know it will always be a delete and truncate, vs a merge, i'd import into a new table, and when the import was complete, rename the original table or drop it,and rename the new table to the correct name;you might need to script permissions to the table with the change, but that would minimize the blocking i'd think.Mon, 06 Aug 2012 10:03:03 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1340724-1550-1.aspxHello,I need an advice, I need to get data daily from one server to another, pretty much I will truncate table and insert data, what is the best way to do it, I don't want cause performance, so what is the best way to handle it.Thank youMon, 06 Aug 2012 09:58:15 GMTKrasavita