SQLTeam.com | Weblogs | Forums

Clone Staging-Data to RemoteServer


How could i automate the following Task?: (DWH / Staging-Tables)
Loop through all the tables (from a list), and import the data from the current day (ImportDate = getdate ()) and copy the Data into an identical database on another server. Same DB-Structure (is a clone). Basically, following SQL would have to be generated for all tables:
INSERT INTO RemoteServer. [Dbo]. [Customer]
(ColumnList ...)
ColumnList ....
FROM LocalServer. [Dbo]. [Customer]
WHERE ImportDate = getdate()‌

Realize with SSIS, C #, T-SQL / Merge, SQL Server Tools, Replicate ....

Nicole :roll_eyes:


We run the code ON the Remote Server instead and PULL (rather than PUSH) the data using OpenQuery ... something like this

DECLARE @SQL NVarchar(MAX), @LastTransfer datetime

SELECT @LastTransfer = LastTransfer
FROM dbo.MyConfigTable
WHERE SomePKey = 'xxx'

SELECT @SQL = 'SELECT Col1, Col2, ... 
FROM RemoteDatabase.dbo.RemoteTable 
WHERE ColChangeDate >= ''' + CONVERT(varchar(30), @LastTransfer, 126) + ''''

INSERT INTO dbo.MyLocalStagingTable
FROM OpenQuery(RemoteLinkedServerName, @SQL)

SELECT @LastTransfer = MAX(ColChangeDate)
FROM dbo.MyLocalStagingTable
WHERE ColChangeDate < GetDate() -- Do not include any accidental far-future values!

SET LastTransfer = @LastTransfer
FROM dbo.MyConfigTable AS U
WHERE SomePKey = 'xxx'