Dis-similar database IO

DB Questions again. *SIGH*

I have a ProvideX Database that contains new product information.
Each entry has a unique job number (Two fields actually)

I am building a new MFG SQL Server Database that needs to import new jobs from the ProvideX Database on demand.

What is the fastest method to determine the jobs that have not been absorbed in the SQL database?

Ideally I would write a flag of some kind to the ProvideX database that I would use in my initial Query to get only those jobs that are new.
The bad news is I cannot write to the ProvideX DB. It restricts access to read only.

Currently I am reading a list of job numbers out of the ProvideX, Storing them in an array, doing the same with my SQL database, then loop through the list removing those that exist in the SQL database.

Once I have my list I do a heavy query (Lots of fields) for the new jobs and push them into my SQL DB.

I'd like to keep the time I tie up the DBs to a minimum.

Am I on the right track or is there a better method?

~T

I'm not sure why you'd need to pull the job numbers from the SQL database into an array. My suggestion would be to write the ProvideX job numbers to a temporary table in SQL Server, then do a join to the SQL Server table you're putting the data into. Delete any rows in the temp table that have a match in your target table; then your temp table is left containing only the new, unmatched data that you need to import.

Sounds good Laura. When you say 'Write the ProvideX job numbers' how do you do such a thing without storing the numbers first?

Are you suggesting looping through the ProvideX recordset writing each one to the temp table or something else?

---
BTW I still have an interest in this thread but I am going with a different solution.

All this effort was to try to automate job initialization in the new DB for sales.

I just found out that there is no criteria I can rely on to query for NEW entries in the ProvideX database. The jobs can be new for less than a minute before their status is changed to something else.

I think this means that the only way I can be sure the job does not exist in the new DB is to check every job in the one against every job in the other.

Instead I am asking the sales people who initiate new jobs to enter the new job number into my application. Armed with a specific job number my routines can pull exactly the job data required across to the new DB.

Since I added job numbers to our paperwork as barcodes all they will have to do is scan them. No big whoop. :)

Sounds like your new solution is much better. :) If the data is business-critical, you might consider setting up a nightly/weekly/whatever process to audit the two databases to ensure that they stay in sync (in case a sales person forgets to scan an item).

When you say 'Write the ProvideX job numbers' how do you do such a thing without storing the numbers first?
I just meant get them into SQL Server using any standard import method, such as a DTS package. Rolling your own app to loop through and insert one by one is going to generally be quite a bit less efficient than using the built-in database tools designed for the purpose of high-volume data manipulation.

The new database and my application will be the tool to contol Manufacturing Tracking and Control for all production.

So if the job isn't in the system they will not be able to get the product built.
Further if they pass the paper router to the next step and the job isn't in the new system the department will just send the router back to sales.

We are keeping the old system to use for all financials so it isn't going away ever that I know of.
---

DTS? D(ata) T(ransfer) S(???)
New one on Me.:confused:

Data Transfer Services :D