这篇论坛文章主要介绍了Oracle数据库到SQL Server数据库主键的迁移过程具体内容请参考下文
由于项目需要要将以前Oracle的数据库转化为SQL Server今天利用SQL
Server的DTD进行数据库的迁移但导入以后发现只导入了表结构和数据而表的一些主键约束都没导过来感觉很郁闷而手头又没有好的迁移工具如Erwin所以动手写了个小工具基本实现了主键的转移主要代码如下
主要控件
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数据库的连接
连接Oracle:
ADOConnOrcaleConnectionString :=Format(ORAConnStr[trim(EdtDataBaseText)
trim(EdtUserText)trim(EdtPassText)]);
try
ADOConnOrcaleOpen;
MsgBox(Oracle数据库连接成功!);
Except
MsgBox(Oracle数据库连接失败!);
end;
连接SQL Server:
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;
