I have the following tables on a non-SQL database that I can access via
ODBC:

Updated
OrderHeader
OrderDetails
InvoiceHeader
InvoiceDetails

When an order or an invoice is added/updated a record with the order number
is added to the Updated table. I need to loop through the
Updated table and for each record bring the data over to the corresponding
tables on the SQL server.

Does anyone have any ideas how to do this? I was attempting to do this with
a DTS job, but I don't know how to pass the order number to each
update and then loop and get the next order. I am open to other ideas also.

Any help would be appriciated. Let me know if you need any more information.

Thanks,
Mike

I don't know as I like the use of the word loop as it brings to mind
cursors.

I would do this

We know that the only rows in which we are interested are those in the
Updated table.

We therefore have 4 DataPumps from the source that bring over the details
from our tables into a scratch working area

SELECT <col List> FROM OrderHeader WHERE OrderNumber IN (SELECT OrderNumber
FROM Updated)
....
...
..

We now have 4 working tables with the data we need sat next to our real
versions of the tables

We can then issue

Updates first where keys match

UPDATE OrderHeader
SET................................
FROM OrderHeader JOIN ScratchOrderHeader
ON OrderHeader.OrderNumber = ScratchOrderHeader.OrderNumber


Now the inserts

INSERT OrderHeader(<col List>)
SELECT <col list> FROM ScratchOrderHeader LEFT OUTER JOIN OrderHeader
ON ScratchOrderHeader.OrderNumber = OrderHeader.OrderNumber
WHERE OrderHeader.OrderNumber IS NULL


Do that for each table.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Mike" <mbaith (AT) yahoo (DOT) com> wrote

I've got a similar situation, only imagine that the "Updated" table
resides on the SQL Server and the other tables on the non-SQL Server.
How would I do that?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Allan,

Thank you for your input on this. You have helped greatly.

Mike

Does your "other" dbms have the concepts of Primary keys, OUTER JOINS ?

If yes then there is no problem.

If no then we would need to be a bit more cunning

We could create a linked server of the "Other" dbms from our SQL Server and
issue the statements through that.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Mary Kerrigan" <mkerrigan (AT) ktoys (DOT) com> wrote

It's an AS/400 system. How would I do this?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You can create a linked server to the AS/400 and do it the way I mentioned.
That's the easiest way.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Mary Kerrigan" <mkerrigan (AT) ktoys (DOT) com> wrote