How to copy data from one DB in sql server to another DB in another Sql server?

See more:

SQL

C#3.5

How to copy data from one DB in sql server to another DB in another Sql server? I am using Archival SP to implement the same where datas from Transaction are archived based on the Archival Date while data in the Master Table isn't . I am using Linked Server for data transfer between the DB in 2 sql server instances. I want another script of SP in SQL Server to do this other than Linked Server.

My Archival SP is as follows:

CREATE   PROCEDURE [dbo].[DIB_ARCHIVE]
(
   @LIVEDB        NVarchar(100)
 , @ARCHIVEDB     Varchar(100)
 , @ArchiveDate    VARCHAR(10)
 , @ArchiveAgeDate VARCHAR(10)
)
AS

DECLARE @TableName Varchar(200)
Declare @DateField Varchar(50)
Declare @TableType char(1)
Declare @STRIDENTITY NVarchar(4000)
Declare @STRINSERT NVarchar(4000)
Declare @STRDELETE NVarchar(4000)
declare @ColumnList varchar(MAX)
declare @STRUSE Nvarchar(100)
declare @DISABLECONSTRAINT Nvarchar(1000) declare @ENABLECONSTRAINT Nvarchar(1000) Declare @Archive_LogID INT Declare @NumRowsChanged INT

SET @DISABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @DISABLECONSTRAINT SET @DISABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @DISABLECONSTRAINT

set @STRUSE='USE '+@LIVEDB
EXECUTE sp_executesql @STRUSE


---Archive LOg-------------
       INSERT INTO  Archive_Log  (LiveDBName,ArchivalDBName,Archival_Date,ArchivalStartTime)
       VALUES (@LIVEDB,@ARCHIVEDB,getdate(),getdate())
       SET @Archive_LogID= SCOPE_IDENTITY() ---Archive LOg-------------

BEGIN TRY
BEGIN TRAN

IF CURSOR_STATUS('global','ARCHIVE')>=-1
BEGIN
DEALLOCATE ARCHIVE
END

DECLARE ARCHIVE CURSOR FOR SELECT TableName,DateField,TableType FROM dbo.Archive_config OPEN ARCHIVE fetch next from ARCHIVE into @TableName, @DateField,@TableType

WHILE @@FETCH_STATUS = 0
BEGIN

SET @STRIDENTITY=''
set @ColumnList=''
set @STRINSERT=''

--**********SQL to SET IDENTITY_INSERT ON**********--
IF Exists (select 1 from  sys.all_columns c  INNER JOIN sys.tables t  ON t.object_id = c.object_id
                 WHERE is_identity = 1 AND t.type = 'U' and Object_name(t.object_id)=@TableName)
SET @STRIDENTITY='SET IDENTITY_INSERT '+  @ARCHIVEDB +'.dbo.'+@TableName + ' ON '
--**********SQL to SET IDENTITY_INSERT ON**********--

IF(@TableType='M') --Master Table
  BEGIN
     --**********Delete all rows from Master/Meta Table**********--
     SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName
     EXECUTE sp_executesql @STRDELETE
     --**********Delete all rows from Master/Meta Table**********--


     ---Archive LOg-------------
     SET @NumRowsChanged=@@ROWCOUNT
      INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
        VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )
     ---Archive LOg-------------


     --**********Insert to Archive Table from  Live Master/Meta Table**********--
     select @ColumnList=@ColumnList+ ',' + column_name from information_schema.columns  where
      table_name = @TableName  and DATA_TYPE not in ('timestamp')
     set  @ColumnList= RIGHT(@ColumnList,Len(@ColumnList)-1)

     SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @ColumnList + ')
                      SELECT '+ @ColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName
     EXECUTE sp_executesql @STRINSERT
     --**********Insert to Archive Table from  Live Master/Meta Table**********--

     ---Archive LOg-------------
     SET @NumRowsChanged=@@ROWCOUNT
      INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
        VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )
     ---Archive LOg-------------

  END

ELSE IF(@TableType='T') --Transaction Table
  BEGIN
       --**********Insert to Archive Table from  Live Master/Meta Table**********--
     select @ColumnList=@ColumnList+ ',' + column_name from information_schema.columns  where
      table_name = @TableName  and DATA_TYPE not in ('timestamp')

    set  @ColumnList= RIGHT(@ColumnList,Len(@ColumnList)-1)
    SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @ColumnList + ')
                      SELECT '+ @ColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName + '
                      WHERE '+ @DateField + '
                      < CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'


    EXECUTE sp_executesql @STRINSERT
     --**********Insert to Archive Table from  Live Master/Meta Table**********--

     ---Archive LOg-------------
     SET @NumRowsChanged=@@ROWCOUNT
      INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
        VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Transaction Table) : ' +@TableName +' , Archival DB  : '+ @ARCHIVEDB )
     ---Archive LOg-------------

      --**********Delete rows from Master/Meta Table base on date range**********--
     SET @STRDELETE='delete from '+ @LIVEDB +'.dbo.'+@TableName + '
      WHERE '+ @DateField + '
     < CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'

     EXECUTE sp_executesql @STRDELETE
     --**********Delete all rows from Master/Meta Table base on date range**********--

      ---Archive LOg-------------
     SET @NumRowsChanged=@@ROWCOUNT
      INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])
        VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Transaction Table) : ' +@TableName +' ,Live DB : '+ @LIVEDB )
     ---Archive LOg-------------


  END


FETCH NEXT
from ARCHIVE into @TableName, @DateField,@TableType END CLOSE ARCHIVE DEALLOCATE ARCHIVE COMMIT TRAN

---Archive LOg-------------

UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Successfully Completed '
WHERE  Archive_LogID=@Archive_LogID

---Archive LOg-------------

PRINT 'Successfully Completed'
END TRY
BEGIN CATCH
  ROLLBACK TRAN

---Archive LOg-------------

UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Archive Script Failed at Table : '+  @TableName +' , Error Message: ' + ERROR_MESSAGE()
WHERE  Archive_LogID=@Archive_LogID

---Archive LOg-------------
  PRINT 'Archive Script Failed at Table : '+  @TableName +' , Error Message: ' + ERROR_MESSAGE()

END CATCH

SET @ENABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @ENABLECONSTRAINT SET @ENABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @ENABLECONSTRAINT