|
因为项目必要要将早年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; （编辑：河北网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
