Transfer content between local table to remote table

All we need is an easy explanation of the problem, so here it is.

I’ve got 2 SQL instances that I need to transfer data between.
Both instances have a database that is essentially the same, the intent is to transfer data from the Production used one to the other which will act as an archive of sorts (this is to keep the amount of records in the one applications point to at a minimum for efficiency).

The source database is used very heavily, with inserts coming in approx. every 2-3 seconds.
My original thought was to do something like the below so it could be a single transaction, however it would appear this can’t be done with a remote table (via linked server)

With X as (
select col1, col2, col3 from SRCDB.table where <conditions>
) Delete from X
Output deleted.col1, deleted.col2, deleted.col3 INTO destDB.table

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m not sure about the size of the table, but have you looked for nightly bcp export and import as an option.
You could run delete on source separately by batching.

DROP TABLE IF EXISTS [tempdb].[dbo].sourceTable
DROP TABLE IF EXISTS [tempdb].[dbo].staging_SourceTable
DROP TABLE IF EXISTS [tempdb].[dbo].targetTable
GO

--Your source table
CREATE TABLE [tempdb].[dbo].sourceTable(id int identity(1,1) Primary Key, fname char(10), lname char(10), createddate datetime2(0) default getdate())
GO
INSERT INTO [tempdb].[dbo].sourceTable(fName, lName) VALUES ('fName1','lName1')
INSERT INTO [tempdb].[dbo].sourceTable(fName, lName) VALUES ('fName2','lName2')
INSERT INTO [tempdb].[dbo].sourceTable(fName, lName) VALUES ('fName3','lName3')
GO

--Stage data before BCP out, so you can use this to delete from source
CREATE TABLE [tempdb].[dbo].staging_SourceTable(id int)
GO
INSERT INTO [tempdb].[dbo].staging_SourceTable
SELECT ID --Choose primary key or any unique column
FROM [tempdb].[dbo].sourceTable 

--BCP out command, I’m using staging table in my query
EXEC master..xp_cmdshell 
'bcp "SELECT s.id, s.fname, s.lname, s.createddate FROM [tempdb].[dbo].sourceTable s JOIN [tempdb].[dbo].staging_SourceTable stg on s.id = stg.id" queryout C:\temp\outputFile.bcp -S localhost -c -T -b 1000'

--Create schema on target instance
CREATE TABLE [tempdb].[dbo].targetTable(id int, fname char(10), lname char(10), createddate datetime2(0))
GO

--BCP in command
EXEC master..xp_cmdshell 
'bcp [tempdb].dbo.targetTable in C:\temp\outputFile.bcp -S localhost -c -T -b 1000'

--At this point you are safe to delete from source
WHILE 1 = 1
 BEGIN
    SELECT TOP 1000 ID 
    INTO #temp 
    FROM tempdb.dbo.staging_SourceTable

    IF @@ROWCOUNT = 0
        BREAK

    DELETE s
    FROM tempdb.dbo.sourceTable s
    JOIN #temp t on s.id = t.id

    DELETE stg
    FROM tempdb.dbo.staging_SourceTable stg
    JOIN #temp t on stg.id = t.id
    
    DROP TABLE #temp

 END

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply