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


Listing 1

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

Listing 2

:0,$s/$/ ’);


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


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
use oracle sqlloader , it is MUCH faster

Product Spotlight
Product Spotlight 

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

©Copyright 1998-2024  |  Page Processed at 2024-04-13 10:26:46 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search