SqlServer2012 Importing Oracle Detailed cases

The first time you use SqlServer2012, the interface and VS2012 style are the same, great!

To get to the point, this blog post mainly write a case of your own, using SqlServer2012 's "Import and export data" function to import data tables to oracle11g, this machine is loaded with SqlServer2012 and oracle11g client, The Oracle instance is installed on the server 192.168.40.115.

1. Open the Import and Export Data tool, the first to select a data source, and the local server name to write the dot "." or "localhost", authentication select the first one, then select the name of the database to export, then click Next.

　　

2. In the "Destination" drop-down box, select ". Net Framework Data Provider for Oracle" (the SqlServer2012 version of the Import Export Data tool removes the previous version of Microsoft ODBC for Oracle), In the password and user IDs, fill in the password and username logged in to the Oracle database, and fill in the DataSource column in the following format (no semicolon at the tail):

(Description= (address= (protocol=tcp) (host=192.168.40.115) (port=1521)) (Connect_data= (SERVICE_NAME=ORCL)))

When you're finished, click "ConnectionString" in the blanks to generate it automatically. Click Next.

　　

3. If the connection is unsuccessful, you will be prompted:

　　

If the connection succeeds, it goes to the next step:

　　

If it is not an entire table export, then select the second option, use the SQL statement to filter the exported data, this is the whole table export, so click Next directly.

4. Select the desired exported table.

　　

Double-click the corresponding button for the selected table to open the Column Mappings window,

　　

Then click on the "Edit SQL" button,

　　

There is a small problem with the SQL statement that the tool automatically generates in Oracle to create tables, that is, the table and column names are double-quoted, and the resulting table and column names are double-quoted, but if the SQL statement is modified correctly here, the result of the final export is to create only the table that succeeded, but the data transfer fails. So choose the default SQL statement execution and finally change the table name and column name in Oracle.

5. Because Oracle does not have an int type, it needs to be converted, depending on the situation, select how to handle the error, and then click Next.

　　

6. This step is nothing to say, click "Done", then do the data export work.

　　

7. Results of successful execution

　　

8. Finally, you need to rename the table and column names with double quotation marks in PL/SQL, and use two of them, respectively:

"ALTER table old_table_name RENAME to new_table_name;--renaming tables

　　ALTER TABLE [table_name] RENAME COLUMN [COLUMN_NAME] to [new_column_name];--Rename column]

Concluding remarks: This is the way to import data to Oracle through the ". Net Framework Data Provider for Oracle" approach, because the tool itself is flawed and ultimately requires a small processing of the results, which is generally easy to do.

SqlServer2012 Importing Oracle Detailed cases