I have a list of job numbers in a SQL table. I want to write a SQL statement that can be dropped into a DTS component that will use the job numbers in the SQL table to down load data from DBII. How can I accomplish this? Can I create a "Join" between the two tables? Do I need to do a transaction step process where I pick a job number from the SQL list and create a SQL statement to get the information for that job, then step to the next job? Do I need to put the SQL table on DBII so I can do the join and transfer in SQL

I would like to use the most effective/efficient method to get the data. If have an example of the SQL code, that would be great!

Thanks...

Nearly everything you have said will work.

1. You can use linked servers to hook up the DB2 box to your SQL Server:

Easy statement to write, possibly slow (OPENQUERY() is better IMHO than 4
part naming)

2. Put the job numbers you want up to the DB2 box and use a DB2 query to
pull the data

Number of jobs relatively small so fast and the query being performed on DB2
should be quick

3. Bring the DB2 stuff down to SQL Server

Yuck. Too much data and you have no dynmaic filter

4. Loop using a Rowset.

Nope. This will most probably kill you for speed as essentially you will be
looping row*row


I would be testing 1 and 2 myself for speed.




--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Martin" <mehughes (AT) srpnet (DOT) com> wrote

numbers in the SQL table to down load data from DBII. How can I accomplish
this? Can I create a "Join" between the two tables? Do I need to do a
transaction step process where I pick a job number from the SQL list and
create a SQL statement to get the information for that job, then step to the
next job? Do I need to put the SQL table on DBII so I can do the join and
transfer in SQL?

I have a similar problem, but I do not have access to the external server. (I cannot load my MSSQL table out to the DB2 (Oracle in my case) server) I am planning on using the OPENQUERY option, but I need to know if the statement will pull all rows from the linked server and then perform the WHERE on my local server. This would not be acceptable as the linked server is a financial data warehouse with millions of rows. I am hoping that the OPENQUERY will only bring across the rows that match the criteria of my local table. Is this the case? Thanks in advance....

Here is my query statement

SELECT
FROM OPENQUERY([SAICCDW], 'SELECT * FROM VW_EMPLOYEE') Rowset_
WHERE (FISCAL_YEAR IN (SELECT FISCAL_YEAR FROM DW_CURRENT_PERIOD) AND PERIOD IN (SELECT PERIOD FROM DW_CURRENT_PERIOD) AND CYCLE IN (SELECT CYCLE FROM DW_CURRENT_PERIOD) AND EMP_NO IN (SELECT EMP_NO FROM OLTP_EMPLOYEE)

All tables in the where are in my local dbase and VW_EMPLOYEE is in the linked server.