熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

講解Oracle到SQLServer主鍵遷移

2013-11-13 15:55:33  來源: Oracle 

  這篇論壇文章主要介紹了Oracle數據庫到SQL Server數據庫主鍵的遷移過程具體內容請參考下文
  由於項目需要要將以前Oracle的數據庫轉化為SQL Server今天利用SQL
Server的DTD進行數據庫的遷移但導入以後發現只導入了表結構和數據而表的一些主鍵約束都沒導過來感覺很郁悶而手頭又沒有好的遷移工具如Erwin所以動手寫了個小工具基本實現了主鍵的轉移主要代碼如下

      [b]主要控件[/b]
  ADOConnOrcale: TADOConnection; //連接Oracle
  ADOConnSQLServer: TADOConnection; //連接SQL Server
  O: TADOQuery; //連接Oracle
  S: TADOQuery; //連接SQL Server
  S: TADOQuery; //連接SQL Server
  ProgressBar: TProgressBar; //進度條
  Memo: TMemo; //顯示出錯信息
  EdtServer: TEdit; //服務器
  EdtDataBase: TEdit; //數據庫名稱
  EdtUser: TEdit; //用戶名
  EdtPass: TEdit; //口令
  Button: TButton; //執行按鈕
  //常量
  const
  ORAConnStr=Provider=MSDAORA;Data Source=%S;User ID=%S;Password=%S;Persist Security Info=True;
  SQLConnStr=Provider=SQLOLEDB;Data Source=%S;Initial Catalog=%S;User ID=%S;Password=%S;Persist Security Info=False;
  在執行前先進行Oracle和SQL Server數據庫的連接
  [b]連接Oracle:[/b]
  ADOConnOrcaleConnectionString :=Format(ORAConnStr[trim(EdtDataBaseText)
  trim(EdtUserText)trim(EdtPassText)]);
  try
  ADOConnOrcaleOpen;
  MsgBox(Oracle數據庫連接成功!);
  Except
  MsgBox(Oracle數據庫連接失敗!);
  end;
  [b]連接SQL Server:[/b]
  ADOConnSQLServerConnectionString :=Format(SQLConnStr[trim(EdtServerText)
  trim(EdtDataBaseText)trim(EdtUserText)trim(EdtPassText)]);
  try
  ADOConnSQLServerOpen;
  MsgBox(SQL Server數據庫連接成功!)
  except
  MsgBox(SQL Server數據庫連接失敗!);
  end;

  主要執行代碼比較亂沒有整理不過實現功能就行了

                procedure TFormButtonClick(Sender: TObject);
      var
      i:Integer;
      FieldN tableN fieldMaa:String;
      begin
      if Not ADOConnOrcaleConnected then
      begin
      MsgBox(請先連接Oracle數據庫!);
      exit;
      end;
      if not ADOConnSQLServerConnected then
      begin
      MsgBox(請先連接SQL Server數據庫!);
      exit;
      end;
      ScreenCursor :=crHourGlass;
      try
      oClose;
      OSQLClear;
      //取oracle表用戶budget的所有主鍵約束信息
      oSQLText := select aCONSTRAINT_NAMEaCONSTRAINT_TYPEaTABLE_NAME bCOLUMN_NAMEbposition +
       from USER_CONSTRAINTS aUSER_CONS_COLUMNS b where aCONSTRAINT_NAME=bCONSTRAINT_NAME +
       and atable_name=btable_name and constraint_type=P and aowner=bowner +
       and lower(aowner)=budget order by atable_namebposition ;
      Oopen;
      tableN:=;
      OFirst;
      ProgressBarMax:=ORecordCount;
      ProgressBarMin:=;
      ProgressBarStep:=;
      ProgressBarVisible :=true;
      for i:= to ORecordCount do
      begin
      sClose;
      SSQLClear;
      //判斷SQL Server表是否存在當前的字段信息
      SSQLText:=SELECT aname AS tanme b* FROM sysobjects a INNER JOIN +
       syscolumns b ON aid = bid +
       WHERE (axtype = U) AND (aname = +Ofieldbyname(table_name)AsString++
      ) and bname= +Ofieldbyname(COLUMN_NAME)AsString++
       ORDER BY bid;
      SOpen;
      //不存在輸出表明和字段名
      if sRecordCount<= then
      begin
      MemoText:=MemoText+#++Ofieldbyname(table_name)AsString++
       字段+Ofieldbyname(COLUMN_NAME)AsString+ 不存在!;
      ONext;
      tableN:=;
      FieldN:=;
      Continue;
      end;
      //是當前表循環讀取主鍵信息
      if (tableN=) or (tableN= Ofieldbyname(table_name)AsString) then
      begin
      FieldN:=FieldN+[+Ofieldbyname(COLUMN_NAME)AsString+];//表明相同或初試時
      tableN:= Ofieldbyname(table_name)AsString;
      end
      else
      begin
      with S do
      begin
      try
      //取SQL Server表的主鍵信息
      Close;
      sqlClear;
      sqlText:=SELECT * FROM INFORMATION_SCHEMAKEY_COLUMN_USAGE WHERE TABLE_NAME=+tableN+;
      Open;
      first;
      aa:=fieldbyname(constraint_name)AsString;
      //如果該主鍵在SQL表中已存在刪除該主鍵信息重建該表主鍵
      if recordcount> then
      begin
      sqlClear;
      SQLText:=ALTER TABLE +tableN+ DROP CONSTRAINT +aa; //刪除主鍵
      ExecSQL;
      end;
      SQLClear; //COLUMN_NAME
      SQLText:=ALTER TABLE +tableN+ WITH NOCHECK ADD +
       CONSTRAINT [PK_+tableN+] PRIMARY KEY NONCLUSTERED +
       ( + copy(FieldNlength(FieldN))+
       );
      ExecSQL;
      FieldN:=[+Ofieldbyname(COLUMN_NAME)AsString+];
      tableN:= Ofieldbyname(table_name)AsString;
      Except
      MemoText :=MemoText+ +tableN+ 字段 +FieldN+ 導入出錯!;
      exit;
      end;
      end;
      end;
      ProgressBarStepIt;
      ApplicationProcessMessages;
      ONext;
      end;
      MsgBox(導入完成!);
      finally
      ScreenCursor :=crDefault;
      ProgressBarVisible :=False;
      end;
      end;  


From:http://tw.wingwit.com/Article/program/Oracle/201311/17525.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.