If you are planning to transfer the data from one db server to another db server regular basis, You should plan to design Change Data Capture (CDC) which allow you to move changed data from source db to Destination server. I have implemented and tested completely with below sql scripts. You can also free to use this one.
-- STEP 1
-- Enable Database for CDC
USE [AdventureDB]
GO
EXEC sys.sp_cdc_enable_db
GO
use [AdventureDB]
GO
ALTER TABLE dbo.[Employee]
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
GO
--STEP2
USE [ChangeDB]
GO
CREATE TABLE [dbo].[CHG_Employee](
[EMP_ID] [varchar](12) NOT NULL,
[SYS_CHANGE_OPERATION] [char](1) NOT NULL,
[SYS_CHANGE_VERSION] [bigint] NOT NULL,
[Created_Date] [datetime] NOT NULL,
[Upload_Status] [char](1) NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHG_Employee] ADD DEFAULT (getdate()) FOR [Created_Date]
GO
ALTER TABLE [dbo].[CHG_Employee] ADD DEFAULT ('N') FOR [Upload_Status]
GO
-- STEP3
USE [ChangeDB]
GO
CREATE PROCEDURE [dbo].[usp_CT_CHG_Employee]
AS
/*Description : Change Tracking on [AdventureWorksDB] database to Employee table */
BEGIN
Declare @last_synchronization_version bigint;
Declare @ChangNum bigint ;
SELECT @ChangNum = max(SYS_CHANGE_VERSION) FROM [ChangeDB].dbo.[CHG_Employee]
-- print @ChangNum
IF Exists (select 1 FROM [ChangeDB].[dbo].[CHG_Employee] )
Begin
-- PRINT '1 ONE'
INSERT INTO [ChangeDB].dbo.CHG_Employee (EMP_ID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)
SELECT
CT.EMP_ID,
CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_VERSION
FROM
CHANGETABLE(CHANGES AdventureDB.dbo.Employee, @last_synchronization_version) AS CT
WHERE CT.SYS_CHANGE_VERSION > @ChangNum
End
ELSE
Begin
-- PRINT '0 ZERO'
INSERT INTO [ChangeDB].dbo.CHG_Employee (EMP_ID ,SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)
SELECT
CT.EMP_ID,
CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_VERSION
FROM
CHANGETABLE(CHANGES [AdventureDB].dbo.Employee, @last_synchronization_version) AS CT
End
END
GO
-- exec [dbo].[usp_CT_CHG_Employee]