从Oracle数据库到SQL Server数据库主键的迁移

主要控件：

    adoconnorcale: tadoconnection;  //连接oracle
    adoconnsqlserver: tadoconnection; //连接sql server
   
    o1: tadoquery;  //连接oracle
    s1: tadoquery; //连接sql server
    s2: tadoquery; //连接sql server

    progressbar1: tprogressbar;  //进度条
    memo1: tmemo;  //显示出错信息
    edtserver: tedit;  //服务器
    edtdatabase: tedit; //数据库名称
    edtuser: tedit;  //用户名
    edtpass: tedit;  //口令

    button1: tbutton;  //执行按钮

//常量
const
  oraconnstr='provider=msdaora.1;data source=%s;user id=%s;password=%s;persist security info=true';
  sqlconnstr='provider=sqloledb.1;data source=%s;initial catalog=%s;user id=%s;password=%s;persist security info=false';

在执行前先进行oracle和sql server数据库的连接。

连接oracle:

  adoconnorcale.connectionstring :=format(oraconnstr,[trim(edtdatabase.text),
         trim(edtuser.text),trim(edtpass.text)]);
  try
    adoconnorcale.open;
    msgbox('oracle数据库连接成功！');
  except
    msgbox('oracle数据库连接失败！');
  end;

连接sql server:

  adoconnsqlserver.connectionstring :=format(sqlconnstr,[trim(edtserver.text),
          trim(edtdatabase.text),trim(edtuser.text),trim(edtpass.text)]);
  try
    adoconnsqlserver.open;
    msgbox('sql server数据库连接成功！')
  except
    msgbox('sql server数据库连接失败！');
  end;

主要执行代码，比较乱，没有整理，不过实现功能就行了。

procedure tform1.button1click(sender: tobject);
var
  i:integer;
  fieldn, tablen, fieldm,aa:string;
begin
  if not adoconnorcale.connected then
  begin
    msgbox('请先连接oracle数据库！');
    exit;
  end;
  if not adoconnsqlserver.connected then
  begin
    msgbox('请先连接sql server数据库！');
    exit;
  end;
  screen.cursor :=crhourglass;
  try
    o1.close;
    o1.sql.clear;
    //取oracle表用户budget的所有主键约束信息
    o1.sql.text :=' select a.constraint_name,a.constraint_type,a.table_name, b.column_name,b.position '+
                  ' from user_constraints a,user_cons_columns b where a.constraint_name=b.constraint_name '+
                  ' and a.table_name=b.table_name and constraint_type=''p'' and a.owner=b.owner '+
                  ' and lower(a.owner)=''budget'' order by a.table_name,b.position ';
    o1.open;
    tablen:='';
    o1.first;
    progressbar1.max:=o1.recordcount;
    progressbar1.min:=0;
    progressbar1.step:=1;
    progressbar1.visible :=true;
    for i:=0 to o1.recordcount -1 do
    begin
      s2.close;
      s2.sql.clear;
      //判断sql server表是否存在当前的字段信息
      s2.sql.text:='select a.name as tanme, b.* from sysobjects a inner join '+
                   ' syscolumns b on a.id = b.id '+
                   ' where (a.xtype = ''u'') and (a.name = '''+o1.fieldbyname('table_name').asstring+''''+
                   ') and b.name= '''+o1.fieldbyname('column_name').asstring+''''+
                   ' order by b.id';
      s2.open;
      //不存在，输出表明和字段名
      if s2.recordcount<=0 then
      begin
        memo1.text:=memo1.text+#13+'表：'''+o1.fieldbyname('table_name').asstring+''''+
                    '   字段：'''+o1.fieldbyname('column_name').asstring+'''  不存在！';
        o1.next;
        tablen:='';
        fieldn:='';
        continue;
      end;
     //是当前表，循环读取主键信息
     if (tablen='') or (tablen= o1.fieldbyname('table_name').asstring) then
     begin
       fieldn:=fieldn+'['+o1.fieldbyname('column_name').asstring+'],';//表明相同或初试时
       tablen:= o1.fieldbyname('table_name').asstring;
     end
     else
     begin
       with s1 do
       begin
         try
           //取sql server表的主键信息
           close;
           sql.clear;
           sql.text:='select * from information_schema.key_column_usage where table_name='''+tablen+'''';
           open;
           first;
           aa:=fieldbyname('constraint_name').asstring;
           //如果该主键在sql表中已存在，删除该主键信息,重建该表主键
           if recordcount>0 then
           begin
             sql.clear;
             sql.text:='alter table '+tablen+' drop constraint '+aa; //删除主键
             execsql;
           end;
           sql.clear;                              //column_name
           sql.text:='alter table '+tablen+' with nocheck add '+
                      ' constraint [pk_'+tablen+'] primary key  nonclustered '+
                      ' (  '+ copy(fieldn,1,length(fieldn)-1)+
                      ' )';
           execsql;
           fieldn:='['+o1.fieldbyname('column_name').asstring+'],';
           tablen:= o1.fieldbyname('table_name').asstring;
         except
           memo1.text :=memo1.text+'表：  '+tablen+'  字段：  '+fieldn+'  导入出错！';
           exit;
         end;
       end;
     end;
     progressbar1.stepit;
     application.processmessages;
     o1.next;
    end;
    msgbox('导入完成！');
  finally
    screen.cursor :=crdefault;
    progressbar1.visible :=false;
  end;
end;