Using ODBC to guide data from SQL Server to Oracle

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.