reg data transfer

[vamsioracle]

hi all



i have a doubt. If i want to transfer data from one table to a new table in oracle i use the following syntax

create new_table as (select col1,col2 from old_table);



suppose if the table is in sql server and i need only few columns from sql server to my oracle table, how can i do that.


thank u
vamsidhar p

[amitpatel66]

hi all



i have a doubt. If i want to transfer data from one table to a new table in oracle i use the following syntax

create new_table as (select col1,col2 from old_table);



suppose if the table is in sql server and i need only few columns from sql server to my oracle table, how can i do that.


thank u
vamsidhar p

Check the link on how to connect to sql server from oracle

Once you make the setup as mentioned in the link, you can use below command to create new table:

CREATE TABLE <table_name> AS SELECT col1,col2 FROM <tab_name>;

[amitpatel66]

Another simpler way of doing is:

1. Download the data from SQL SERVER table (only the columns that are required) in to flat file (delimited version).
2. CREATE TABLE table_name in ORACLE (with required columns)
3. Use SQL LOADER to upload data to Oracle Table

You can also use EXTERNAL TABLE in ORACLE to refer to the data from flat file. Advantage of using EXTERNAL TABLE is that when ever the data in the flat file changes, the data in the oracle table also changes.