Two-stage population of each row in a table

Question

Two-stage population of each row in a table

David F. Straker

Say Hey Kid

Points: 679
More actions
March 14, 2006 at 6:00 am

#67311

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.

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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.

David F. Straker Say Hey Kid Points: 679 More actions · Answer 2

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.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

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.

David F. Straker Say Hey Kid Points: 679 More actions · Answer 4

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.