I've got two Oracle tables. These two tables have a one-to-one relationship, We will call them MessageData and MessageTransactions. I have a need to use DTS to obtain new messages to these tables (I'm setting a status in the MessageTransactions to make this easy).
I have two SQL Server 2000 tables with the same relationship. I'm needing to use DTS to obtain a record from the MessageData table, put it into the SQL Server version of this table (which will not have the same primary key), then go out and grab the Transaction data that matches it and place it in the MSSQL table that corresponds to the Oracle MessageTransactions table.
I can get both sets of data if I use an inner join, but I've failed to find a way to map the data to two SQL tables, not just one. I'm probably making this far more difficult than it should be, but my final "stab" at it was to have a trigger insert the second set of data (which requires me to setup a linked server to the Oracle database, which I would like to avoid if possible).
Thanks in advance (and sorry for the newbie question).