Replication of Data with SQL Query

Replication of Data with SQL Query

use the following query for the replication of data.
After creating the Stored procedure, schedule a job to run after certain time period.



ALTER PROCEDURE [dbo].[P_GBR_CDR_REPLICATE]
AS
BEGIN
DECLARE @TSQL NVARCHAR(1000)
DECLARE @MAXID BIGINT


WHILE (1=1)
BEGIN


      SELECT @MAXID=  MAX(CDR_SPECIFIC_ID) FROM GBRVIEW.DBO.CDR201106(NOLOCK)
      --SELECT @MAXID


      SELECT   @TSQL = N'
      INSERT INTO CDR201106 (CDR_SPECIFIC_ID,MSISDN,CHARGING_TIMESTAMP,B_NUMBER,DURATION,ENDUSER_CHARGE,RECORD_TYPE)
      SELECT *
       FROM OPENQUERY([LEB-ODSMSSQL2\ODSMSSQL2],''SELECT TOP 10000 CDR_SPECIFIC_ID,MSISDN,CHARGING_TIMESTAMP,B_NUMBER,DURATION,ENDUSER_CHARGE,RECORD_TYPE FROM
      GBRVIEW.DBO.CDR_0 (NOLOCK) WHERE CDR_SPECIFIC_ID >' + CONVERT(VARCHAR(10),@MAXID) +' AND MONTH(CHARGING_TIMESTAMP)=06'')'
      --SELECT @TSQL
      EXEC  SP_EXECUTESQL @TSQL
     


      IF @@ROWCOUNT=0
            BREAK;


END
END