August 6, 2012 at 9:58 am
Hello,
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 you
August 6, 2012 at 10:03 am
if 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.
Lowell
August 7, 2012 at 10:06 am
I 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
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)
-- initializations
SELECT
@destinationDatabase = 'Copy_' + @sourceDatabase
,@dataPath = CASE WHEN RIGHT(@dataPath, 1) <> '\' THEN @dataPath + '\' ELSE @dataPath END
,@logPath = CASE WHEN RIGHT(@logPath, 1) <> '\' THEN @logPath + '\' ELSE @logPath END
IF OBJECT_ID('tempdb..##t_TableList') IS NOT NULL
DROP TABLE ##t_TableList
CREATE TABLE ##t_TableList (
TableNameSYSNAME NOT NULL
)
IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL
DROP TABLE ##t_ColumnList
CREATE TABLE ##t_ColumnList (
ColumnListVARCHAR(MAX) NOT NULL
)
SET NOCOUNT ON
BEGIN 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)
-- DEBUGSELECT * 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)
END
END TRY
BEGIN 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
-- Cleanup
SELECT @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_TableList
IF OBJECT_ID('tempdb..##t_ColumnList') IS NOT NULL
DROP TABLE ##t_ColumnList
August 7, 2012 at 9:29 pm
Truncate and insert????? then why we cant go for BCP? Will it not be faster?
August 7, 2012 at 10:08 pm
BCP 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).
November 13, 2012 at 11:22 am
I can't use truncate, I need to delet over 5 mil records and leave about 2mil
November 13, 2012 at 1:33 pm
Delete rows in batches and don't forget to rebuild the indexes after deletes based on fragmentation percentage.
November 13, 2012 at 6:24 pm
This article should help http://www.sqlservercentral.com/articles/T-SQL/67898/.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply