Migrating from an Oracle database to a SQL Server database primary key

oracle|server| Data | Database because the project needs to convert a database of previous Oracle to SQL Server, the database is migrated using SQL Server DTD today, but after import, only the table structure and data are imported. and table some of the primary key constraints did not guide over, feel very depressed, and there is no good migration tools, such as Erwin, so hands-on wrote a small tool, the basic implementation of the primary key transfer, the main code is as follows:

Primary controls:

Adoconnorcale:tadoconnection; Connecting Oracle
Adoconnsqlserver:tadoconnection; Connecting to SQL Server

O1:tadoquery; Connecting Oracle
S1:tadoquery; Connecting to SQL Server
S2:tadoquery; Connecting to SQL Server

Progressbar1:tprogressbar; Progress bar
Memo1:tmemo; Show error messages
Edtserver:tedit; Server
Edtdatabase:tedit; Database name
Edtuser:tedit; User name
Edtpass:tedit; Password

Button1:tbutton; Execute button

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

The Oracle and SQL Server databases are connected prior to execution.

Connect Oracle:

Adoconnorcale.connectionstring: =format (Oraconnstr,[trim (Edtdatabase.text),
Trim (Edtuser.text), Trim (edtpass.text)]);
Try
Adoconnorcale.open;
MsgBox (' Oracle database connection succeeded! ');
Except
MsgBox (' Oracle database connection failed! ');
End

To connect to SQL Server:

Adoconnsqlserver.connectionstring: =format (Sqlconnstr,[trim (Edtserver.text),
Trim (Edtdatabase.text), Trim (Edtuser.text), Trim (edtpass.text));
Try
Adoconnsqlserver.open;
MsgBox (' SQL Server database connection succeeded! ')
Except
MsgBox (' SQL Server database connection failed! ');
End

The main execution code, more messy, no collation, but to achieve the function on the line.

Procedure Tform1.button1click (Sender:tobject);
Var
I:integer;
FIELDN, Tablen, fieldm,aa:string;
Begin
If not adoconnorcale.connected then
Begin
MsgBox (' Please connect Oracle database First! ');
Exit
End
If not adoconnsqlserver.connected then
Begin
MsgBox (' Please connect SQL Server database first! ');
Exit
End
Screen.cursor: =crhourglass;
Try
O1. Close;
O1. Sql. Clear;
Take all PRIMARY KEY constraint information for Oracle table user 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. A;
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;
Determine if the current field information exists in the SQL Server table
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;
Does not exist, output indicates and field name
If S2. Recordcount<=0 Then
Begin
memo1.text:=memo1.text+ #13 + ' table: ' +o1.fieldbyname (' table_name '). asstring+ ' ' +
' Field: ' +o1.fieldbyname (' column_name '). "asstring+" does not exist! ';
O1. Next;
Tablen:= ';
Fieldn:= ';
Continue;
End
Is the current table that iterates through the primary key information
if (tablen= ') or (tablen= o1.fieldbyname (' table_name '). asstring) Then
Begin
fieldn:=fieldn+ ' [' +o1.fieldbyname ' (' column_name '). asstring+ '], ';//show Same or preliminary
tablen:= o1.fieldbyname (' table_name '). asstring;
End
Else
Begin
With S1 do
Begin
Try
Take the primary key information for the SQL Server table
Close;
Sql. Clear;
Sql. Text:= ' SELECT * from INFORMATION_SCHEMA. Key_column_usage WHERE table_name= ' ' +tablen+ ';
Open;
First
Aa:=fieldbyname (' constraint_name '). asstring;
If the primary key already exists in the SQL table, delete the primary key information and rebuild the table primary key
If Recordcount>0 Then
Begin
Sql. Clear;
Sql. text:= ' ALTER TABLE ' +tablen+ ' DROP CONSTRAINT ' +aa; Delete primary key
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+ ' table: ' +tablen+ ' field: ' +fieldn+ ' import error! ';
Exit
End
End
End
Progressbar1.stepit;
Application.processmessages;
O1. Next;
End
MsgBox (' Import complete! ');
Finally
Screen.cursor: =crdefault;
Progressbar1.visible: =false;
End
End