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 
    ) Delete from X
    Output deleted.col1, deleted.col2, deleted.col3 INTO destDB.table
    


  • I’m not sure about the size of the table, but have you looked for nightly https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/aa337544(v=sql.105) 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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2