March 14, 2006 at 6:00 am
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.
March 14, 2006 at 7:45 am
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.
March 14, 2006 at 8:03 am
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.
March 14, 2006 at 8:11 am
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.
March 14, 2006 at 8:53 am
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