Importing data from one server to another
-
I'm trying to determine the best approach to import data from a database on one sql server (2000) to a different database on another sql server (2000), based on a data match between a table on the first and a table on the second database.
Server/db 1 has a range of name data I need in table A. I also need person attribute data from the same database from a joined table B. I would then like to import that into Server/db 2, into a separate table, based on a match with name data in table C in that db 2. The match should occur on server 1, as I want to limit the number of records retrieved.
Do I need a linked server set up for this or could I do this without that? How about a DTS package? Would that be possible? And would it filter out the limited results on server 1 or would it pull everything across to server 2 first and then filter out the matches? I've looked into data driven queries but they seem a bit tricky, plus I don't know whether they would be best for the job.
Thanks in advance
-
personally, i think linked servers are the best way to tackle this;
a link to ServerA allows you to join to serverB , then filter the data based on the join and other criteria in a WHERE statement.
I do this all the time, and it's quick and affective; the query can then be saved for use as a schedule job(or service broker in S2k5) if it's going to happen a lot.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply