Consolidating Data from multiple servers

Question

Post reply

Consolidating Data from multiple servers

TSQLExplorer

Ten Centuries

Points: 1279
More actions
December 12, 2005 at 4:03 pm

#76461

Hello all. I need to build a consoliidated table to query on and update it frequently from 19 disparate servers I can connect to. The obvious way is to set up DTS packages to import and append to the tables, but it's so painful to set these up .. and I'm thinking they will be obsolete when we go to 2005.. so I'm wondering, is there a means using TSQL or some other way that I can do this? Bottom line.. trying to take 18 tables and append them into 1 to produce statistical data.

Thanks all!

P.S. ... if this went in the wrong forum.. please advise!

Thanks again.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ND-215452 SSCrazy Points: 2017 More actions · Answer 1

ND-215452

SSCrazy

Points: 2017

December 12, 2005 at 8:26 pm

#609132

why you don't you output results from each server into a file and then append into your table?

Del Murray Right there with Babe Points: 789 More actions · Answer 2

We need a little more info to give a good solution.

1. Where are the servers located ? local or remote?

2. How do you connect to them ... over the internet or WAN via VPN?

3. You say you need to "update". Does that mean the derived table needs to be updated? If so, does the source table also need to be updated and worse yet, if you update the derived table can the updated data be discarded when you bring in new data ?

4. Do you want to recreate the derived table every time or just add the new records and what if data changes on the source server .. do you need that change also moved to your derived table

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 3

The servers are remote on a T1 WAN. No VPN involved. By Update, I mean overwrite the contents of the derived table with the most recent data... replace.. not append.. because we do demographic counts, etc. So, for example, when I go to school X and re-populate their data to the derived table, I want all current records in the derived table to go away and be replaced by the new. In all cases the source table stays untouched and intact.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

Presumably your "derived table" has a column that indicates which source system the data came from ?

There is a pretty generic pattern that can solve this, and it can be accomplished via linked servers versus DTS. You need a "staging area", which may or may not be a separate physical database on your server.

eg:

-- Prepare the staging table for new records from the remote source

Truncate Table staging.dbo.[SomeTableName]

-- Get all records from the remote source using a linked server and

-- 4-part object naming

Insert Into staging.dbo.[SomeTableName]

Select * From [LinkName].[DBName].[Owner].[SourceTableName]

-- Empty the derived table of all records from the source that's

-- being refreshed

Delete From dbo.[YourDerivedTable]

Where DataSource = [SomeSourceIdentifier]

-- Repopulate the 'derived table' from the staged remote data

Insert Into [YourDerivedTable]

Select [WhateverColumns] From staging.dbo.[SomeTableName]

Everything in sqare braces needs to be customized for each remote source, but the pattern is the same - truncate staging, repopulate staging, delete existing, repopulate

Why a staging area ? It is not strictly needed if the data is clean and maps well, you could just delete the derived table data and insert directly form the linked server. However, you usually find at some point that you need to do some data cleansing and manipulation before bringin the dta in, and the staging area is where you'd do that step.