Re: Bring Data into SQL Server

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 06:20:48 +0100

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

Relevant Pages

  • Re: Access Migration to SQL
    ... yeah because Tom's a little crybaby pussy who doesn't know how to use SQL Server ... First thing to check, make sure that all the primary keys are correctly defined for each tables and that if you have multiple indexes for a table, that the primary key is clusterized and if it's not, that there are no other index clusterized and that the name for the primary key is the first one in alphabetical order. ... If it's still too slow after that, you will have to dig further: use Terminal Server is you are on a very slow LAN or on a VPN, change the design of your frontend in order to retrieve the minimal number of records.; create and use Views, use passthrough queries, create your own recordsets or use unbound forms, switch to ADP or even better, switch to .NET. ... For ADP, take a look at all the previous posts in this newsgroup; for unbound forms, take a look at the books from Mary Chipman and Baron or from Klander, for creating your own recordsets, see http://support.microsoft.com/?kbid=281998. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access Migration to SQL
    ... you need to move it to SQL Server, and then rewrite all your queries, sprocs and views on the database side and not on the clientside ... First thing to check, make sure that all the primary keys are correctly defined for each tables and that if you have multiple indexes for a table, that the primary key is clusterized and if it's not, that there are no other index clusterized and that the name for the primary key is the first one in alphabetical order. ... If it's still too slow after that, you will have to dig further: use Terminal Server is you are on a very slow LAN or on a VPN, change the design of your frontend in order to retrieve the minimal number of records.; create and use Views, use passthrough queries, create your own recordsets or use unbound forms, switch to ADP or even better, switch to .NET. ...
    (microsoft.public.access.adp.sqlserver)
  • Processing a cube with less than clean data
    ... None of the tables had primary keys, ... I have no control over the process that populates the data in ... was to set it up to use SQL Server authentication. ... BI project to ServerA (SQL Server 2005) and populating it with data ...
    (microsoft.public.sqlserver.olap)
  • Re: Im completely stuck.............
    ... don't use Identity columns as primary keys. ... Your app can generate the primary key value without any help from SQL Server ... When the user wants to add a child row, use the Guid as the foreign key. ... SQL doesn't autogenerate the Guids, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Left Joins with Linked Tables
    ... there are indeed cases where JET is not able to handle outer joins ... rather than allenbrowne at mvps dot org. ... > Both of the queries are just a single table - filtered. ... > Both of the tables are linked from a SQL Server. ...
    (microsoft.public.access.queries)