Best practice to pull data from sql server 2000 to sql server 2005 with dynamic queries

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

If you need to query sql server 2000 database to migrate data, then sql server 2005 import/export wizard might be a good one. Please take a look at http://msdn2.microsoft.com/en-us/library/ms141209.aspx. If you don't need to do query, bcp utility might be a good candidate. Please take a look at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

Thanks,

Junfeng

|||

First, set up the SQL 2000 server as a 'Linked Server' for the SQL 2005 server (See Books Online for details about Linked Servers.)

Then, using 'four-part naming conventions', you can just query between the two servers.

This example, when executed on the SQL 2005 server, would take data from the SQL 2000 server and insert it into the SQL 2005 server:

Code Snippet

INSERT INTO MyTable (Col1, Col2, Col3, etc.)
SELECT Col1, Col2, Col3, etc.
FROM MySQL2000Server.MyDatabase.dbo.MyTable
WHERE MyID IN ( 1, 2, 5, 10, 25 )

|||

Hi There,

Here's my requirements...

I have to run a query B against database B with results (list of ids) from a query A run against database A and then push the results back to database A. Query B is constructed dynamically from the results obtained from Query A. So I wouldlike to know what would be the best way to achive this?

Thanks a lot!!