Two-stage population of each row in a table

  • Hi all

    Got a problem.

    We are assembling DTS packages for an Oracle to SQL Server migration. One of the tables that has to be migrated has to be done in two stages, with stage 1 taking across the columns that form part of the core application & stage 2 taking across the other customer-specific columns. We are taking different columns - but the same rows - in each stage.

    e.g. the table has the following columns:

    column1 [primary key]

    column2

    column3

    column4

    column5

    The first stage (which is just a straightforward Data Transformation task) takes column1, column2 & column3 from the table on the Oracle box & inserts them into the matching table on the new SQL Server box.

    The second stage has to take column4 & column5 from the Oracle box & update the columns in the matching rows on the new SQL Server box to their values.

    How can I go about doing this? I'm something of a newbie where DTS is concerned & am all at sea here - so any help & guidance would be much appreciated.

    Thanx

    Dave.

  • I do this quite often. For your primary step you can simply pump the data in the main table. For the second step pump the rows in a separate Temprary table (often called a staging table). For simplicity my staging table is a mirror image of the primary table. Then use sql tasks that call stored procedures that update rows in the primary table from the staging table.

  • Hi Ray

    Many thanx for that.

    One question - do you have any idea of what the update stage's performance will be like?

    The table in question is very large - holding several hundred thousand rows - and the migration will have to be carried out within a fairly tight time limit.

    The table has a primary key, but no other constraints, indexes or triggers.

    Dave.

  • Well, that was an initial guess, it should update okay. but.

    Question are the customer records from a different table?, on the same database, different database, and same server or different server?

    If all the data is all on the same server, you can create a pump task using sql instead of a table copy.

    So just select required records from main table, join to customer records and select required records from there. and pump all at once.

     

  • Hi Ray

    OK. The core & customer records are in the same table on both the SQL Server & the Oracle databases.

    We could pump all at once - but for reasons of source code control, we can't do the customer columns in the same package as the core application columns. Or vice versa.

    Dave.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply