Transfer content between local table to remote table

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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:tempoutputFile.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:tempoutputFile.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

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