April 23, 2002 at 6:39 am
Hi I need to DTS tables from Oracle to SQL Server.
Oracle Tables have their own Primary Key and
SQL Server Tables have their own Primary Key,
The Primary Key column is copied to the SQL Server Table and has a unique constraint in it.
There is a master detail relationship in the SQL Server.
When I DTS the master table, the Primary Key is autogenerated(Identity Column)
I need the value of the Identity column of newly inserted rows inserting data into the detail table.
Thus the "non-key" columns of the detail table come from Oracle where as the key comes from SQL Server Master Table.
How Do I achieve this?
I need to rollback the Master Transformations in case the Detail Transformations fail.
Thanking you in Advance,
Regards,
RB
April 23, 2002 at 10:41 am
You need some other key from Oracle. Otherwise there is no way to know what the PK will be. I've run into this before, trying to move data from SQL->SQL. Replication and other items do not help. However, if you have a GUID or other type of key, then you can match things up yourself.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 23, 2002 at 11:12 am
Why not just set you PK field to non-autogenerated and since PK's are unique the value comming in from Oracles PK should be fine. Or you may consider doing an extra column on the end of your table. With the import it will be hard to get the data matched up the way you describe.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 12:48 am
quote:
Why not just set you PK field to non-autogenerated and since PK's are unique the value comming in from Oracles PK should be fine. Or you may consider doing an extra column on the end of your table. With the import it will be hard to get the data matched up the way you describe."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Hi ,
Thanx for the Response.
But the Oracle Guys has put a 4 column Primary Key with some columns being numeric and some being alpha.
So to get all of that into 1 column at SQL Server, we looked at DTS.
At the same time, I personally do not believe in Alpha Primary Keys.
I believe that Numeric Primary keys lend better to the Indexes and comparisons are faster.
Which is why we added the SQL Server Primary Key.
This had to be AutoNumber else we would have to take care of simulatenous users doing max operation on the Primary and getting the same value.
This again pointed to AutoNumber.
Let me be more elaborate on the problem that I face.
In DTS, I can enable transactions, disable Auto Commit, set Transaction Isolation to "read uncommitted"
Using this, I tried the following.
DTS Master Records from Oracle to SQL
While DTSing Details Records, Use DTSLookup to get the SQL Primary Key from the Oracle Primary Key thru Active Scriping.
Now we have all colums.
Insert into the Details Table.
Ideally this looks to be FoolProof.
But Some how my Data in Details Table in not being DTSed.
I wonder If it is because I have Disabled Auto Commit.
But my Master Data has come in.
In fact while debugging a small set of data,
I realised that even though there we 2 Detail records for 1 master record.
Somehow the 1st record did not even get processed.
The second got processed but the Table does not show it.
I think somehow I will need to commit the Detail Transactions. I have no idea on how to.
Secondly if Non-comitted transactions are the problem, how are the Master Records comming In.
Any further responses would be appreciated and most welcome.
Regards,
Rahul
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply