Oracle converts sqlserver

Recently, a Project Database of the company was converted to SQL Server (2000 or 2005) as a database of Oracle. At the beginning, I found some information on the Internet, I found a tool called swissql. I tried it and failed to find it again. later, I was reminded by my colleagues that using SQL Server's import and export tools can indeed be converted. the procedure is as follows.

The required environment is relatively simple, that is, the local machine can connect to Oracle and SQL server at the same time. Note: you do not need to create ODBC for the Oracle database. You can create a new SQL Server database by using direct connection, used to load ORACLE data. the following uses sqlserver2005 as an example.

L step 1

Open sqlserver,-1, right-click the target database, and choose task> import data.

Figure-1

L step 2:

Select the "Import Data" menu. A wizard is displayed. Click Next. The page shown in-2 is displayed.

Figure 2

It is recommended to select the Microsoft ole db provider for Oracle method. If you select another method to import data, an error is returned.

Enter the server name and user password. After the test is successful, check "Allow To Save Password"

L step 3

This step selects the target database, as shown in-3.

Figure-3

Step 4

There are two ways to import data. One is to visually select a DB table or view, and the other is to use an SQL statement. Generally, the first method is to import data in batches.

Figure 4

L step 5

This step selects the DB object to be imported, as shown in-5.

Figure 5

If the column data type of the Oracle table does not match successfully, you can click the edit button to edit it directly.

After selecting the imported dB object, click "Next" or "finish" to start the import. Generally, if the data is normal and not special ORACLE data, it can be imported.

I want to remind you of the problems I encountered during the operation.

1. After the database table is imported, the primary key or foreign key may be lost. We need to create the primary key and write the SQL syntax by ourselves.

2. Blob or clob data in Oracle cannot be imported here. We need to manually writeCodeImport. For example, in table A, we can first use the above method to import data from non-blog or clob fields, then write the code, execute the update statement, and write the field value into it.

3. if the data format does not meet the conditions, it is not possible. For example, for a date field, Oracle may also store 0200-9-12, and an error will be reported for this line of data during import, because it does not conform to the date type format, it needs to be adjusted.

4. The conversion from Oracle to sqlserver2000 or sqlserver2005 produces different results. Please note that 2005 is more accurate than 2000, whether it is data or table structure.

In short, this import is still relatively smooth. More importantly, it is relatively simple and much easier than using third-party tools. Therefore, some users who need this method are recommended.