|
Home > Archive > MS SQL Server DTS > September 2005 > Updating records
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hi all,
I need to update records in a sql table based on records in an oracle table.
The update needs to be done where OracleTable.C_Ref = SqlTable.C_Ref. I am
unable to get the databases linked and there are 14 million records in the
oracle table and 200,000 records in the sql table. Can someone advise of the
best way to do this in a dts package.
| |
| Allan Mitchell 2005-09-30, 7:23 am |
| OK so you have a couple of ways
You can TXFR the rows from Oracle to a SQL Server staging table and then
do a TSQL update. Although there are 14 million rows in the Oracle
table have they all been updated or are new? If not then a WHERE Clause
would restrict them and cut dwn on bandwidth.
You can also, and this is probably more preferable, use a linked server.
This will allow you to query the Oracle DB as though it was local
(almost).
Use a DDQ. This is row*row based and in my opition no better than #1
--
----------------------------------------
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com
"M4TT" <M4TT@discussions.microsoft.com> wrote in message
news:3343C6FE-922B-44EA-9C2A- 3F575C66F704@microso
ft.com:
> Hi all,
>
> I need to update records in a sql table based on records in an oracle table.
>
> The update needs to be done where OracleTable.C_Ref = SqlTable.C_Ref. I am
> unable to get the databases linked and there are 14 million records in the
> oracle table and 200,000 records in the sql table. Can someone advise of the
> best way to do this in a dts package.
|
|
|
|
|