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!!

Best Practice - Lookup or SQL from Variable?

Hi,

I am pulling data from FoxPro tables into SQL 2005, and want to only pull new or changed rows. Accordingly each table in Fox has a column LastChangedDateTime, indicating the last time the row was updated, and I have a table in SQL which has one row per Fox table, listing the table name and the most recent data pulled into SQL.

In 2000 DTS I would have pulled the SQL datetime value into a package variable, then used a parameterized SQL statement with ".. WHERE LastChangedDateTime > ? " to select the rows I require.

In SSIS this approach does not seem to be possible, and the options are that I either use a variable for the entire SQL statement or, as the first SSIS tutorial suggests, use a lookup against the SQL table.

Gut feel is that the lookup will perform slower than creating the variable SQL and executing that (given that the source table is 13 million rows and rising, and I only want the last 100,000 or so from today).

What is considered best practice under these circumstances?

Also is it possible to write SSIS scripts in C# rather than VB.NET, as the syntax differences are driving me mad? ;-)

Thanks in advance,

Richard R

I would go with the DTS style method, it should work just fine. An Exec SQL Task can get the date value and store it in a variable. The variable can then be used in a parameterised query, in the same way as you did with DTS, but obviously using a Data Flow task, and the correct source. Saying that I have not tried it with FoxPro, but you will be using the same OLE-DB driver I assume so it should work fine. Parameter support is available in the OLE-DB Source, and the driver should support it if it did in DTS.

Using a lookup would not make sense as you will be doing far more work.
Using a variable for the command (with EvaluateAsExpression = True) is also perfectly valid, and sometimes the better choice, but for a simple query like this and since you have parameter support, I'd go with the former method, but there is nothing in it really.

The Script Task and Script Component both use Visual Studio for Applications (VSA), which means you get the power of .Net rather than a interpreted script language. Unfortunately VSA has only been implemented for VB.Net, there is no C# support. No idea if or when there will be either, but you are certainly not the first to raise the issue.

|||

Thanks Darren,

Sometimes it's good to check out a gut feeling - just in case the whole underlying system architecture has changed.

I'm not sure the FoxPro v9 driver OLEDB actually has parameter support, it didn't seem to work when I tried it, hence the original post. This is the first time I've had to interface to FoxPro, and there are definitely a few oddities about the process...

Regards,

Richard

Best approach with DTS

Let me see if I can explain this.

I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.

Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.

Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.

Step 3
The returned rows here, need to be limited to key values returned from step 2

Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.

What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.

I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.

What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.

I'm open to ideas for alternatives