By using the above mentioned method, a user can create a useful insert script for inserting millions of records into a table in oracle. When the user is provided with millions of records to insert into a table, they can be easily inserted through an insert script comprising of an equal number of insert statements. The use of SQL loader can make the task easy, but the goal can be achieved even without it. This method can be taken as a substitution for SQL loader. It uses a substitution utility of UNIX.

1.    Unix Operating System

2.    A list of raw data arranged in a sequential manner.

This is a VI editor command specific to UNIX operating system. It is usually used to substitute texts in VI editor. The basic formula is described below.

parameter1,parameter2s/parameter3 (text to be altered or location involved)/parameter4(final text to be put)

parameter1: Indicates the first line from which the substitution will take place.

0/1 indicates the first line of any file in VI editor. 2 indicate the second line and so on.

Parameter2: Indicates the last line up to which the substitutions will occur. (the count of the final line)

S: Stands for the substitution.

Parameter3: Indicates the part of the text in each line to be substituted or the place of substitution (usually prior to the beginning (^) or after end ($) of the line).

Parameter4: This indicates the final text that the user wishes to put in desired places.

Listing 1

:0,$s/^/Insert into <table_name> values (‘

Listing 2

:0,$s/$/ ’);

Explanation

The following figure shows the raw data that has been put into a file.

Figure 1

Here there are 500 records that the user has been given initially in a excel sheet. They have been put in to a file and the file has been opened in VI editor for processing. In order to prepare the insert script, the syntax for the script {i.e. insert into <table_name> values(‘ } has to be placed  in each line prior to the record. Listing 1 shows just how to do the same.

The following figure shows the initial part of the line being processed.

Figure 2

Now pressing the Enter key will do the substitutions shown in the following figure.

Figure 3

 
Now listing 2 comes into the picture as shown below.

Figure 4

Pressing the Enter key will substitute the last part of the records making the script ready to be run.

Figure 5

Conclusion

The insert script is now ready to be executed. Following the method, millions of records can be easily processed to prepare a useful script. The rule also applies for any DML operation.