February 16, 2005 at 10:43 pm
Hi,
Urgently a require has come and I have to transfer some tables and datas from oracle to sql server.But I don't know how to do it.Because I didn't do it before.I have to do this from SQL Server.Can anyone tells me what are the steps should I follow?
Try to give solution asap.It' very urgent!!!!
Thanks & Regards
Niladri
Niladri Kumar Saha
February 16, 2005 at 10:47 pm
Use DTS of SQL Server
My Blog:
February 16, 2005 at 11:01 pm
You have to configure DTS for this.How to configure DTS for this?
Niladri
Niladri Kumar Saha
February 17, 2005 at 1:39 am
Hai,
Check whether this works. In DTS select Microsoft OLE DB provider for oracle
and enter the Oracle DB name, Password and assign permissions or using ODBC connection
for ORACLE.
February 17, 2005 at 7:45 am
Nilardi,
This should be fairly simple with DTS.
-create a new Local Package
-Create a connection to The Oracle DB (Icons on top left)
-Create a connection to the Sql Server
-Click transform stat task icon (in lower left box)
-Select source connection, destination connection
-double click this task (the line between the connections)
-select source table on 1st tab
-select destination table on 2nd tab (or have DTS create it)
-clean up any transformations on 3rd tab
-make new tasks for each table you want to import
If all is good , execute the package.
Watch for data-types etc
February 17, 2005 at 9:27 am
If this is a 1 time deal, you don't even need DTS. You go under the Security item in EM, setup a linked server to Oracle. Once that is setup, you can simply run INSERT INTO statements:
INSERT INTO dbo.SQLServerTable (column list)
SELECT (same column list)
FROM YourLinkName..OracleSchemaOwner.OracleTableName
If you need to pre-create the tables, create them empty with WHERE 0 = 1 clauses
eg
SELECT *
INTO NewSqlTableName
FROM YourLinkName..OracleSchemaOwner.OracleTableName
WHERE 0 = 1 -- Create empty table, same structure as Oracle table
(Actually, if you don't mind holding locks for potentially long periods, you can dispense with the WHERE 0 = 1 and create and populate the table in 1 step)
February 17, 2005 at 11:45 pm
Of course, before you can do any of that, you need to install the Oracle Client software (The network portion) and using the Net Configuration Assistant, add a local net service name.
Then using import from SQL server is a simple task.
5ilverFox
Consulting DBA / Developer
South Africa
February 17, 2005 at 11:45 pm
Of course, before you can do any of that, you need to install the Oracle Client software (The network portion) and using the Net Configuration Assistant, add a local net service name.
Then using import from SQL server is a simple task.
5ilverFox
Consulting DBA / Developer
South Africa
February 17, 2005 at 11:45 pm
Of course, before you can do any of that, you need to install the Oracle Client software (The network portion) and using the Net Configuration Assistant, add a local net service name.
Then using import from SQL server is a simple task.
5ilverFox
Consulting DBA / Developer
South Africa
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply