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

由于项目需要要将以前Oracle的数据库转化为SQL Server,今天利用SQL Server的DTD进行数据库的迁移，但导入以后发现只导入了表结构和数据，而表的一些主键约束都没导过来，感觉很郁闷，而手头又没有好的迁移工具，如Erwin,所以动手写了个小工具，基本实现了主键的转移，主要代码如下：

主要控件：

    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;

原文转自：http://www.ltesting.net