Tuesday, March 20, 2007

Create SQL Insert Script with Inline Korn Script

We had to insert 400+ rows by 7 columns of data into an Oracle database table. Here is the inline script I used to create the SQL script file, which will be used to perform the inserts. By the way, we were having problems using the imp/exp utilities, so we opted for this method.

Change shell to Korn
# ksh
while read -r a b c d e f g
do
printf "%s %s %s %s %s %s %s\n" "insert into db_table values('$a'"",""$b"",""$c"",""$d"",""$e"",""$f"",""$g);"
done<mylist>>insert_file.sql

# more insert_file.sql
insert into db_table values('Phoenix', 95, 89, 89, 99, 89, 93);
insert into db_table values('Tucson', 87, 88, 85, 90, 88, 91);
insert into db_table values('Las Vegas', 83, 85, 85, 84, 85, 89);
insert into db_table values('Reno', 53, null, 55, 54, 55, 59);
...
...

Here's the script version

# vi db_parser.ksh
#!/bin/ksh
while read -r a b c d e f g
do
printf "%s %s %s %s %s %s %s\n" "insert into db_table values('$a'"",""$b"",""$c"",""$d"",""$e"",""$f"",""$g);"
done<$1>>$2.sql
:wq! (saves and quits vi)

Turn on the read and execute bits for all and then run the script.
# chmod 555 db_parser.ksh
# ./db_parser.ksh mylist insert_file

# more insert_file.sql
insert into db_table values('Phoenix', 95, 89, 89, 99, 89, 93);
insert into db_table values('Tucson', 87, 88, 85, 90, 88, 91);
insert into db_table values('Las Vegas', 83, 85, 85, 84, 85, 89);
insert into db_table values('Reno', 53, null, 55, 54, 55, 59);
...
...

# su - oracle
# sqlplus
**you will be prompt for login/password information**
SQL>@insert_file.sql;
--The "@" symbol executes the contents in the insert_file.sql file
--If you have errors/modifications, simply open the insert_file.sql in vi/nedit/textedit/emacs and make the necessary changes
--Then rerun the file using the "@" symbol

No comments: