Importing from Oracle to SQL Server 2012 1

[ImpInTraining]

This is probably a pretty easy question, but I can't be sure. I have very little experience with SQL. I have access to an Oracle server. I have created some identical tables in SQL Server 2012.

ORACLE = Table1 ; Table2 ; etc.
SQL Server 2012 = NEW_Table1 ; NEW_Table2 ; etc.

All of the fields of these copied tables are likewise identical but prepended with a little indicator:

ORACLE = Column1 ; Column2 ; etc.
SQL Server 2012 = NEW_Column1 ; NEW_Column2 ; etc.

What I need to do is develop a script that will import all of the data of the ORACLE tables I am interested in into SQL Server tables. I saw the SELECT INTO command and thought I was on to something, but that will create new tables and I don't really want to create new tables since I already have some in place.

Any suggestions out there?

 

[fredericofonseca]

sqlplus + bcp
ssis
linked server to oracle - direct insert into.. select from


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[JohnDuncanTB]

As long as there are no IDENTITY columns in the NEW_ tables and the columns are of the same datatype as the Oracle tables, then tt may be as easy as ...

INSERT INTO NEW_Table1
SELECT * FROM OracleServer.OracleDatabase.dbo.Table1

You will have to substitute your values for OracleServer and OracleDatabase.

--JD
"Behold! As a wild ass in the desert go forth I to my work."

 

[ImpInTraining]

Thanks... sounds like a good plan. I will give it a try.