Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Question Unanswered: SQL in DTS import

    I have following problem:

    I have imported some order header records from PROGRESS database into DB1 (SQL Server database) with DTS.(=1st import)

    What I need now is to import all order rows records related to the order header records in DB1. (=2nd import)

    Order header records and order rows records are located in separate PROGRESS databases, that is reason for two separate imports.

    Is it possible to create such a SQL query into DTS import, that only those order rows, which have related header records in DB1 are imported ?

    I quess that SQL clause is something like this, but I have no idea, what is the correct syntax in this case....

    ---
    SELECT R.Header_ID, R.Row_ID, R.field1, R.field2, R.field3


    FROM
    DB1 H '(SQL SERVER DATABASE)
    DB2 R '(=PROGRESS DATABASE)

    WHERE
    H.Header_ID=R.Header_ID

    ---

    Thanks a lot (in advance) for replying this issue !



    - Mark

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What is the relationship between the order header and order rows ? What do you want the end result to be in sql server - 2 tables ? How big is the order rows database ?

  3. #3
    Join Date
    Nov 2002
    Posts
    7
    Originally posted by rnealejr
    What is the relationship between the order header and order rows ? What do you want the end result to be in sql server - 2 tables ? How big is the order rows database ?


    Order_header.ORDER_ID = Order_row.HEADER_ID (one to many relationship). DTS should create new table into DB1 and add imported rows to the new table. So I will have two tables in DB1. Order row database has millions of rows, that is reason for this try.

    -Mark

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Just use dts to import the data into a holding table. Use the keys between the 2 tables to create the order_row table. Blow away the holding table. This will be much faster and easier.

    The following is an example of how to create the table from a holding table (I am using northwind as an example):

    select b.* into test from orders a inner join [order details] b on a.orderid = b.orderid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •