Hi,
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 ...)
SELECT
ColumnList ....
FROM LocalServer. [Dbo]. [Customer]
WHERE ImportDate = getdate()
Realize with SSIS, C #, T-SQL / Merge, SQL Server Tools, Replicate ....
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
SELECT *
FROM OpenQuery(RemoteLinkedServerName, @SQL)
SELECT @LastTransfer = MAX(ColChangeDate)
FROM dbo.MyLocalStagingTable
WHERE ColChangeDate < GetDate() -- Do not include any accidental far-future values!
UDPATE U
SET LastTransfer = @LastTransfer
FROM dbo.MyConfigTable AS U
WHERE SomePKey = 'xxx'
