CodeSnip: How to Create an Oracle Insert Script Using Substitution Function in UNIX?
page 4 of 4
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 22585/ 33

Code

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.


View Entire Article

User Comments

Title: use sql loader   
Name: culon
Date: 2008-03-18 7:44:34 PM
Comment:
use oracle sqlloader , it is MUCH faster

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-13 7:28:14 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search