這篇論壇文章（賽迪網(wǎng)技術(shù)社區(qū)）主要介紹了Oracle數(shù)據(jù)庫(kù)到SQL Server數(shù)據(jù)庫(kù)主鍵的遷移過(guò)程，具體內(nèi)容請(qǐng)參考下文。

由于項(xiàng)目需要要將以前Oracle的數(shù)據(jù)庫(kù)轉(zhuǎn)化為SQL Server,今天利用SQL Server的DTD進(jìn)行數(shù)據(jù)庫(kù)的遷移，但導(dǎo)入以后發(fā)現(xiàn)只導(dǎo)入了表結(jié)構(gòu)和數(shù)據(jù)，而表的一些主鍵約束都沒導(dǎo)過(guò)來(lái)，感覺很郁悶，而手頭又沒有好的遷移工具，如Erwin,所以動(dòng)手寫了個(gè)小工具，基本實(shí)現(xiàn)了主鍵的轉(zhuǎn)移，主要代碼如下：

主要控件：

ADOConnOrcale: TADOConnection; //連接Oracle

ADOConnSQLServer: TADOConnection; //連接SQL Server

O1: TADOQuery; //連接Oracle

S1: TADOQuery; //連接SQL Server

S2: TADOQuery; //連接SQL Server

ProgressBar1: TProgressBar; //進(jìn)度條

Memo1: TMemo; //顯示出錯(cuò)信息

EdtServer: TEdit; //服務(wù)器

EdtDataBase: TEdit; //數(shù)據(jù)庫(kù)名稱

EdtUser: TEdit; //用戶名

EdtPass: TEdit; //口令

Button1: TButton; //執(zhí)行按鈕

//常量

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';

在執(zhí)行前先進(jìn)行Oracle和SQL Server數(shù)據(jù)庫(kù)的連接。

連接Oracle:

ADOConnOrcale.ConnectionString :=Format(ORAConnStr,[trim(EdtDataBase.Text),

trim(EdtUser.Text),trim(EdtPass.Text)]);

try

ADOConnOrcale.Open;

MsgBox('Oracle數(shù)據(jù)庫(kù)連接成功！');

Except

MsgBox('Oracle數(shù)據(jù)庫(kù)連接失敗！');

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數(shù)據(jù)庫(kù)連接成功！')

except

MsgBox('SQL Server數(shù)據(jù)庫(kù)連接失敗！');

end;

主要執(zhí)行代碼，比較亂，沒有整理，不過(guò)實(shí)現(xiàn)功能就行了。

procedure TForm1.Button1Click(Sender: TObject);

var

i:Integer;

FieldN, tableN, fieldM,aa:String;

begin

if Not ADOConnOrcale.Connected then

begin

MsgBox('請(qǐng)先連接Oracle數(shù)據(jù)庫(kù)！');

exit;

end;

if not ADOConnSQLServer.Connected then

begin

MsgBox('請(qǐng)先連接SQL Server數(shù)據(jù)庫(kù)！');

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表是否存在當(dāng)前的字段信息

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;

//是當(dāng)前表，循環(huán)讀取主鍵信息

if (tableN='') or (tableN= O1.fieldbyname('table_name').AsString) then

begin

FieldN:=FieldN+'['+O1.fieldbyname('COLUMN_NAME').AsString+'],';//表明相同或初試時(shí)

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+' 導(dǎo)入出錯(cuò)！';

exit;

end;

end;

end;

ProgressBar1.StepIt;

Application.ProcessMessages;

O1.Next;

end;

MsgBox('導(dǎo)入完成！');

finally

Screen.Cursor :=crDefault;

ProgressBar1.Visible :=False;

end;

end;