Data script processing from SQL Server database to Oracle database

In many cases of development, for convenience or commonality of consideration, we first consider the SQL Server database for development, but sometimes the customer's production environment is Oracle or other database, then we need to transform the corresponding data structure and data script to the corresponding database, Data structure in general, the syntax follows the standard of SQL92, or we build the corresponding structure script according to different powerdesigner files, but the actual data of the script we need to do some processing, as well as the replacement of the text, this article combined with notepad++ The text regular expression substitution, implements some date more special data script adjustment, transforms it from SQL Server to the Oracle processing process, this article is the introduction for these whole database processing.

1. Database design file and database structure script

In general, when we do database design, we use database modeling tools like PowerDesigner to design the database design model for SQL Server by default, as shown below.

Of course, if we need a different database, then convert it to the corresponding database, and then make some database type adjustment, as well as the field of case conversion.

In this way we adjust the various database design files as shown below.

Different database design model, then we do some check, mainly database type check, such as the Memo field of the large text should be set to CLOB, the binary should be adjusted to blob, and so on .

For example, for Oracle's database design (converted from SQL Server), it also needs to convert its field names to uppercase, and in PowerDesigner you can perform custom functions for processing.

Where the dialog box selects the script that opens the corresponding uppercase table name, the following action.

For the convenience of everyone, I put it out for use.

'Files: Powerdesigner.ucase.VBs'Version: 1.0'function: Traverse all the tables in the physical model, change the table name, table code, field name, field code all from lowercase to uppercase;'and change the name and code of the sequence from lowercase to uppercase. 'Usage: Open the physical model, run this script (ctrl+shift+x)'remark:'*****************************************************************************DimModel'Current ModelSetModel =ActivemodelIf(model is  Nothing) ThenMsgBox "There is no current Model"ElseIf  notModel. IsKindOf (Pdpdm.cls_model) ThenMsgBox "The current model isn't an physical Data model."Elseprocesstables modelprocesssequences ModelEnd If'*****************************************************************************'function: Processsequences'function: Recursive traversal of all sequences'*****************************************************************************Subprocesssequences (folder)'to process a sequence in a model: lowercase to uppercaseDimsequence for  eachSequenceinchFolder.sequencessequence.name=UCase(sequence.name) Sequence.code=UCase(Sequence.code)NextEnd Sub'*****************************************************************************'function: Processtables'function: Recursive traversal of all tables'*****************************************************************************Subprocesstables (folder)'working with tables in a modelDimTable for  eachTableinchFolder.tablesif  notTable. Isshortcut Thenprocesstable TableEnd ifNext'recursively recursive to subdirectoriesDimsubfolder for  eachSubfolderinchfolder. Packagesprocesstables subfolderNext End Sub'*****************************************************************************'function: processtable'function: Iterates through all the fields of the specified table, changing the field names from lowercase to uppercase.'field code changed from lowercase to uppercase'the table name is changed from lowercase to uppercase'*****************************************************************************Subprocesstable (table)DimCol for  eachColinchtable. Columns'change field names from lowercase to uppercaseCol.code =UCase(col.code) col.name=UCase(Col.name)NextTable.name=UCase(table.name) Table.code=UCase(Table.code)End Sub

After this processing, our table name and field in the PowerDesigner can be converted to uppercase, so that the corresponding table data structure script can be obtained, if more than one table, you can batch build database structure script.

2. Generation of database table data scripts

The above database table structure of the script generation, just part of our database migration script operation, sometimes our actual framework or business system, often have some basic data to write, then we need to build the corresponding data script.

In database script export, we can use many tools such as SQL Server itself to export the SQL script of the data, and we can also use other database management tools, such as Toad for SQL Server or Navicat The database management tool, such as premium, implements export script operations for data.

Then, in the process of generating the script, "the type of data to be scripted" is "data only" in the Advanced options for setting the output, as shown below.

However, using SQL Server generated data scripts by default is not very good for date type conversions, as shown in the following results.

So I use the Navicat Premium tool, which is more visually visible, to manipulate the database's data scripts. The scripts generated using NAVICAT Premium are as follows (only date types are different).

Get the generated data script as shown below.

The rest of the work is for us to do further processing of these data scripts.

3, the data table data script substitution processing

The above describes a tool to get the correct data script, we use NAVICAT Premium or Toad for SQL Server can get a time script similar to the following format.

N ' 2016-06-22 10:35:36.590 '

In order to process the date data for Oracle, we need to convert to

To_date (' 2016-06-22 10:35:36 ', ' yyyy-mm-dd HH24:mi:ss ')

Such a format

So let's do some regular processing of the above script, such as replace: [dbo]. [] N ' is ' for normal text processing, we also need to do regular expression rule processing, such as our date replacement of the regular expression as follows:

' (\d{4}-\d{2}-\d{2}\s*\d{2}:\d{2}:\d{2}) \.\d{3} '
To_date\ (' \1 ', ' yyyy-mm-dd HH24:mi:ss ' \)

as shown below.

The last database script that was replaced with a regular expression is shown below.

4. Data scripts perform operations on Pl-sql developer tools

The above describes how to implement the table data script generation, with these scripts, we need to use Oracle's database management tool pl-sql Developer tool for data import, in order to finalize the entire process. This operation is also somewhat fastidious.

For example, if we create different kinds of database scripts, we just need to join in order or choose to execute database script.

So what do you do with these SQL, do you just drag them onto the pl-sql?

Of course not, otherwise a longer database script could lead to very slow execution efficiency.

There are generally two ways to use the command line.

This is done very quickly and is more efficient than executing directly on the Pl-sql SQL window.

Another way to do this is to use another place in the Pl-sql to execute the database script, as shown below.

In the "Tools" import table operation, a dialog box pops up and is one of the most efficient actions to execute the script.

The method described above is to build the corresponding data objects and data according to the database script without the database.

Data script processing from SQL Server database to Oracle database