你的位置:首页 > 数据库

[数据库]sql server 2005导出数据到oracle


一. 在sql server下处理需要导出的数据库

  1. 执行以下sql,查出所有'float'类型的字段名,手动将float类型改为decimal(18,4).

select 表名=d.name,字段名=a.name,类型=b.name FROM syscolumns a left join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'where b.name in ('float')order by d.name;

View Code

  2. 执行以下sql,查出sql server中的字段名在oracle属于内部关键词,并手动修改.

select 表名=d.name,字段名=a.name FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'where a.name in ('NUMBER','LEVEL','ROW','rowNum','rows','mode') order by a.id,a.colorder

View Code

  3. 查询类型为text并且值为空的字段,设置为'-',否则导出时会报错误(该语句查询出一组sql语句),将查询出来的一组sql语句全部拷贝出来并全部执行.

select 'update '+ d.name +' set '+ a.name +'=''-'' where '+ a.name +' like '''''FROM syscolumns a left join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'where b.name in ('TEXT') order by d.name

View Code

  4. 执行下列存储过程,将所有数据表的列名大写.

/*修改列名大写*/drop procedure columnNameToUppergo/*创建存储过程*/create procedure columnNameToUpperas/*声明变量*/declare @tableName varchar(128),    @columnName varchar(128)/*声明游标*/declare @cursorVar Cursor/*给游标赋值*/set @cursorVar=Cursor Forselect obj.name,upper(col.name) from syscolumns col,sysobjects obj where obj.xtype='U' and obj.id=col.id;/*打开游标*/Open @cursorVar  /*取一条数据赋给变量*/  Fetch Next From @cursorVar Into @tableName,@columnName  /*声明执行语句变量*/  Declare @executeSql nvarchar(2000)  While(@@FETCH_STATUS=0)  Begin    /*获得要执行的SQL语句*/    set @executeSql=N'exec sp_rename '''+@tableName+'.'+@columnName+''','''+@columnName+'''';     /*动态执行存储过程*/    execute sp_executesql @stmt=@executeSql    /*取下一条数据*/      Fetch Next From @cursorVar Into @tableName,@columnName  End/*关闭游标*/Close @cursorVar/*释放资源*/Deallocate @cursorVar/*返回*/Return /*将上一个go到此的存储过程提交数据库执行*/go/*执行存储过程*/execute columnNameToUpper

View Code

  5. 执行下列存储过程,将所有表名大写.

/*修改表名大写*/drop procedure tableNameToUppergo/*创建存储过程*/create procedure tableNameToUpperas/*声明变量*/declare @tableName varchar(128),    @uptableName varchar(128)/*声明游标*/declare @cursorVar Cursor/*给游标赋值*/set @cursorVar=Cursor Forselect obj.name,upper(obj.name) from sysobjects obj where obj.xtype='U' and name!='dtproperties';/*打开游标*/Open @cursorVar/*取一条数据赋给变量*/Fetch Next From @cursorVar Into @tableName,@uptableName/*声明执行语句变量*/Declare @executeSql nvarchar(2000)While(@@FETCH_STATUS=0)Begin  /*获得要执行的SQL语句*/  set @executeSql=N'exec sp_rename '''+@tableName+''','''+@uptableName+'''';   /*动态执行存储过程*/  execute sp_executesql @stmt=@executeSql  /*取下一条数据*/    Fetch Next From @cursorVar Into @tableName,@uptableNameEnd/*关闭游标*/Close @cursorVar/*释放资源*/Deallocate @cursorVar/*返回*/Return 0go/*执行存储过程*/execute tableNameToUpper

View Code


二. 在oracle下以system用户登录进行处理

  1. 创建表空间

create tablespace ST10005(表空间名)datafile 'D:\oracle\db\ST10005.ora' (数据文件存放地点 D:\oracle\db\文件夹要先存在)size 500Mdefault storage(initial 200mnext 50mminextents 20maxextents 500)online; 

View Code

  2. 创建用户 (指定默认表空间 和 临时表空间 指定连接状态)

CREATE USER ST10005(用户名) PROFILE DEFAULT IDENTIFIED BY ep(密码) DEFAULT TABLESPACE ST10005(表空间名) TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

View Code

  3. 为用户授权

grant connect,resource,dba to ST10005(用户名);

View Code


三. 用sql server 2005自带的导入工具将数据导入到oracle

  1. 在要导出的数据库上点击鼠标右键,选择"任务"》"导出数据"

  2. 依次点击下一步,在"选择数据源"页面填写相应选项

    

   3. 在"选择目标"页,目标栏选择"Oracle Provider for OLE DB",点击属性会弹出个数据链接属性的对话框;如下图

数据源一栏填写的是用PLSQL连接时的database;用户名一栏都要用大写的;同时要选中“允许保存密码”选项。

  4. 接着下一步直到选择源表和源视图,选择要导入的表,注意:一次不能选太多的表,否则会弹出一个警告,

    如弹出了警告,就要少选几个表,如果不弹出警告,就下一步直到完成。

  5. 重复1-4步操作几次,将所有的表都导入进去

 

四. 导入完成后,还需执行以下几步操作

  1. 检查表的数目是否正确

    a. sql server下执行

select count(*) from sysobjects obj where obj.xtype='U' and name!='dtproperties';

View Code

    b. oracle下执行

select count(*) from user_tables;

View Code

  2. 建立视图、主键、外键、索引等