Using ODBC to guide data from SQL Server to Oracle

1, first to build the corresponding table in the Oracle database, the field type in Oracle database differs from SQL Server, the field types commonly used in Oracle are VARCHAR2, Integer, nchar, date,sql Server are relatively more , the length of the uniqueidentifier type is 36.

2. Open the Administrative Tools-data source (ODBC) and configure the data source for SQL Server.

3. Open Oracle Database-tool--ODBC Import

4, select the server, enter the database account and password

5. Select users and tables, then click the Import button in the lower left corner to

6. Attach the code that automatically generates oracle from the SQL Server table:

/********1. Defining variables *********/DECLARE @table_id int, @table_name varchar, @column_id int, @c_name sysname, @c_type varch AR (+), @max_length smallint, @precision tinyint, @scale tinyint, @is_nullable bit, @cons_sql varchar (5000)

/********2. Variable initialization *********/Set @table_name = ' Mio_log ' Set @cons_sql = ' CREATE table '[email protected]_name+ ' ('

/********3. id*********/of the table DECLARE tbl_cursor cursor for select a.id from Sys.sysobjects a WHERE a.id = object_id (N ' [dbo] . [' [Email protected]_name+ '] ' and OBJECTPROPERTY (ID, N ' isusertable ') = 1

OPEN tbl_cursor FETCH NEXT from Tbl_cursor to @table_id

/********4. Construct the structure of the table *********/DECLARE columns_cursor cursor for SELECT b.column_id,b.name,c.name,b.max_length, B.precision,b.scale,b.is_nullable from Sys.columns b,sys.systypes c where [email protected]_id and B.system_ Type_id=c.xtype ORDER BY b.column_id ASC

OPEN Columns_cursor

FETCH NEXT from Columns_cursor to @column_id, @c_name, @c_type, @max_length, @precision, @scale, @is_nullable

While @ @FETCH_STATUS = 0 BEGIN set @[email protected][email protected]_name+ "+ (case is @c_type in (' smallint ', ' tinyint ' Then ' int ' when @c_type = ' int ' Then ' number ' when @c_type = ' varchar ' Then ' varchar2 (' +convert (varchar (2), @max_length) + ' If @c_type in (' DateTime ', ' smalldatetime ') Then ' date ' is @c_type = ' decimal ' Then ' number ' + ' (' +convert (varchar (1), @precision) + ', ' +convert (varchar (1), @scale) + ') ' when @c_type = ' char ' then ' char (' +convert (varchar (2), @max_length) + ') ' When @c_type = ' text ' Then ' long '--here to map for further data types Else ' # # ' end) + (case if @is_nullable =1 then ' null ' else ' NOT null ' End ' + ', ' FETCH NEXT from Columns_cursor to @column_id, @c_name, @c_type, @max_length, @precision, @scale, @is_nullable END

/********5. Output Build Table statement *********/Select Left (@cons_sql, Len (@cons_sql)-1) + '); '

/********6. Releasing cursors *********/deallocate tbl_cursor deallocate columns_cursor

Using ODBC to guide data from SQL Server to Oracle