Since the project needs to convert the previous Oracle database into SQL Server, today with SQL Server's DTD is migrated, but the import will only import the table structure and data, and some primary key constraints of the table are not guided, it feels very Depressed, and there is no good migration tool, such as Erwin, so write a small tool, basically realize the transfer of the primary key, the main code is as follows: Main control: adoconnorcale: tadoconnection; // Connect Oracle Adoconnsql Server: tadoconnection; Connect SQL Server O1: TadoQuery; // Connect Oracle S1: TadoQuery; // Connect SQL Server S2: TadoQuery; // Connect SQL Server ProgressBar1: TProgressBar; // Progression Memo1: Tmemo; // Display Error Information EDTSERVER: TEDIT; // Server Edtdatabase: TEDIT; // Database Name EDTUSER: TEDIT; // User Name EDTPASS: TEDIT; // Password Button1: TButton; // Execution 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 '; performs the connection of Oracle and SQL Server databases before execution. Connect Oracle: Adoconnorcale.Connectionstring: = Format (ORACONNSTR, [TRIM (EDTDATABASE.TEXT), TRIM (EDTUSER.TEXT), TRIM (EDTPass.Text)]); try adoconnorcale.open; msgbox ('Oracle database connection success!' EXCEPT MSGBOX ('Oracle Database Connection Failed!'); END; Connection SQL Server: AdoconnsqlServer.connectionstring: = Format (SqlConnstr, [Trim (EdtuServer.Text), Trim (EdtuSer.Text) Trim (edtpass.text)]); try adoconnsqlser.Open; msgbox ('SQL Server database connection success!') Except msgbox ('SQL Server database connection failed!'); END; mainly executed code, more chaos, no finishing However, it is not performed.
Procedure TForm1.Button1Click (Sender: Tobject); VAR i: Integer; Fieldn, Tablen, Fieldm, AA: String; Begin if not adoconnorcale.Connected The begin msgbox ('please contact Oracle Database!'); EXIT; End; IF NOT AdoconnsqlServer.Connected The Begin Msgbox ('Please connect SQL Server Database!'); exit; end; screen.cursor: = cr HOURGLAS; try o1.close; o1.sql.clear; // Take Oracle Table User Budget All primary key constraint information 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; // Determine if the SQL Server table exists in the current field information 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 indicating and field name if s2.recordcount <= 0 THEN BEGIN MEMO1.TEXT: = MEMO1.TEXT # 13 'Table:' '' O1.fieldByname ('Table_name'). Asstring '' '' 'field:' '' O1.fieldbyName ('colorn_name'). Asstring '' 'does not exist! '; O1.NEXT; TABLEN: =' '; Fieldn: ='; Continue; end; // is the current table, loop read primary key information IF (Tablen = O1.fieldByname ('Table_name ') .Sstring) The begin fieldn: = fieldn ' [' O1.fieldByName (' Column_name '). Asstring '], '; // indicates that the same or initial TABLEN: = O1.fieldByname (' Table_name '). Asstring ; end else begin with S1 do begin try // get SQL Server table primary key information 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, rebuild the table master key if Recordcount> 0 dam sql.clear; SQL.Text: = 'ALTER TABLE' TABLEN 'DROP CONSTRAINT' AA; // Removes the primary key execSql; end; sql.clear; // column_name sql.text: = 'alter table' Tablen 'with nocheck add ' ' Construint [PK _ ' TABLEN '] Primary Key Nonclustered '
