Post reply

Importing data from one server to another

  • December 15, 2008 at 4:32 pm

    #155027

    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 🙂

  • December 15, 2008 at 6:49 pm

    #912887

    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 1 (of 1 total)

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