"Mary Kerrigan" <mkerrigan@ktoys.com> wrote in message
news:652faee5.0407211237.1b56981e@posting.google.com...
> 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@no-spam.sqldts.com> wrote in message
news:<OOidH$tbEHA.1292@TK2MSFTNGP11.phx.gbl>...
> > 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@yahoo.com> wrote in message
> > news:uRgWQJrbEHA.1048@tk2msftngp13.phx.gbl...
> > > 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
> > >
> > >

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:<OleS1u6bEHA.3476@tk2msftngp13.phx.gbl>...
> 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