SQL Server database converted to Oracle

Source: http://blog.csdn.net/hzfu007/article/details/6182151

Often encounter situations where SQL Server data needs to be migrated to Oracle.

Look it up online, there are many ways to use third-party tools, etc., but SQL Server itself provides an "import and export" tool that allows you to convert SQL Server data into Oracle.

The following is a simple list of the following steps:

1.

Open the Import Export tool in SQL Server and select the data source on the Select source data interface, which is generally the default. Enter the server IP, user name, password of the source data, select the database, next.

2.

Select the destination interface, here to illustrate, it is best to choose the "MicroSoft OLE DB Provider for Oracle mode (this way there is a disadvantage that if the SQL Server table contains ntext fields, and there are many times, it will not be converted, This time with the Oracle Provider for OLE DB this way, in this way, when the conversion interface, because the default will be a long field to correspond, so you need to manually in the column mapping and transformation interface directly in the table statement to modify the long field to CLOB, and then convert, OK), the mappings for those tables and fields seem less accurate when you choose another method (I don't have to verify the other way). Enter the server name and user password, and after the test is successful, next.

3. In the Specify replication or query interface, select the "Copy tables and views from database" mode, next.

4. In the Select Source table and view interface, select the tables and views that you want to transfer. Then always follow the prompts to do it.

Precautions:

1. The field mappings in these tables may have to be specified manually. However, the general automatic default is possible.

2, SQL Server import into Oracle tables and fields in Oracle Access with SQL statements need to add double quotes to access, this is very troublesome. Because of the need for data migration

These are frequently manipulated. So I used a very simple method of rebuilding all the tables in Oracle and then copying the data in.

Report:

Correspondence between Access, SQL Server, and Oracle data types

The table below shows the ADO Data Type mapping between Access, SQL Server, and Oracle:

DataType Enum	Value	Access	SQL Server	Oracle
adBigInt	20		BIGINT (SQL Server 2000 +)
Adbinary	128		Binary TimeStamp	Raw *
Adboolean	11	YesNo	Bit
Adchar	129		Char	Char
Adcurrency	6	Currency	Money SmallMoney
Addate	7	Date	Datetime
adDBTimeStamp	135	DateTime (Access (ODBC))	Datetime smalldatetime	Date
Addecimal	14			Decimal *
Addouble	5	Double	Float	Float
Adguid	72	ReplicationID (OLE DB), (Access (OLE DB))	uniqueidentifier (SQL Server 7.0 +)
adIDispatch	9
Adinteger	3	AutoNumber Integer Long	Identity (SQL Server 6.5) Int	INT *
Adlongvarbinary	205	OLEObject	Image	Long Raw * Blob (Oracle 8.1.x)
adLongVarChar	201	Memo (Access 97) Hyperlink (Access 97)	Text	Long * Clob (Oracle 8.1.x)
adLongVarWChar	203	Memo (Access (OLE DB)) Hyperlink (Access (OLE DB))	NText (SQL Server 7.0 +)	NClob (Oracle 8.1.x)
Adnumeric	131	Decimal (Access (OLE DB))	Decimal Numeric	Decimal Integer Number SmallInt
Adsingle	4	Single	Real
adSmallInt	2	Integer	SmallInt
Adunsignedtinyint	17	Byte	TinyInt
Advarbinary	204	ReplicationID (Access 97)	VarBinary
adVarChar	200	Text (Access 97)	VarChar	VarChar
Advariant	12		sql_variant (SQL Server 2000 +)	VarChar2
adVarWChar	202	Text (Access (OLE DB))	NVarChar (SQL Server 7.0 +)	NVarChar2
Adwchar	130		NChar (SQL Server 7.0 +)

* in Oracle 8.0.x-decimal and int is equal to number and number (10).

SQL Server database converted to Oracle