Create SQL DML insert statements from file using AWK or similar


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Create SQL DML insert statements from file using AWK or similar
# 1  
Old 07-10-2012
Create SQL DML insert statements from file using AWK or similar

Hi all.

This is my first post on this forum. I've previously found great help in the huge knowledgebase that is here, but this time I have not been able to find a solution to my problem.

I have a large text file that looks like this:
Code:
typedef struct ABC_struct_nbr1_ {
  char    attr1[12+1]; /* KEY=Y, INIT="", MANDATORY=Y */
  char    attr2[12+1]; /* KEY=Y, INIT="", MANDATORY=Y */
  double  attr3; /* KEY=N, INIT=0.0, MANDATORY=Y */
  int     attr4; /* KEY=N, INIT=0.0, MANDATORY=Y */
  char    attr5[20+1]; /* KEY=N, INIT="", MANDATORY=N */
} ABC_struct_nbr1;

typedef struct ABC_struct_nbr2_ {
  char    attr1[10+1]; /* KEY=Y, INIT="", MANDATORY=Y */
  char    attr2[20+1]; /* KEY=Y, INIT="", MANDATORY=Y */
  char    attr3[1+1]; /* KEY=N, INIT="Y", MANDATORY=Y */
} ABC_struct_nbr2;

typedef struct ABC_struct_nbr299_ {
  char    attr1[150+1]; /* KEY=Y, INIT="", MANDATORY=Y */
  int     attr2; /* KEY=N, INIT=0, MANDATORY=Y */
  char*   attr3; /* KEY=N, INIT="", MANDATORY=N */
} ABC_struct_nbr299;

My goal is to turn this file into SQL insert statements like these:
Code:
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default) 
 values('ABC_struct_nbr1','attr1','varchar2',12,'N',1,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default) 
 values('ABC_struct_nbr1','attr2','varchar2',12,'N',2,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default) 
 values('ABC_struct_nbr1','attr3','number',22,'Y',null,'0.0/* KEY=N, INIT=0.0, MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr1','attr4','number',22,'Y',null,'0.0 /* KEY=N, INIT=0.0, MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr1','attr5','varchar2',20,'Y',null,'/* KEY=N, INIT="", MANDATORY=N */');

insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr2','attr1','varchar2',10,'N',1,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr2','attr2','varchar2',20,'N',2,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr2','attr3','varchar2',1,'Y',null,'Y /* KEY=N, INIT="Y", MANDATORY=Y */');

insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr299','attr1','varchar2',150,'N',1,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr299','attr2','number',22,'N',null,'0 /* KEY=N, INIT=0, MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
 values('ABC_struct_nbr299','attr3','clob',null,'Y',null,'/* KEY=N, INIT="", MANDATORY=N */');

These should be used to insert data into a table like this (I include the DDL for your reference):
Code:
create table abc_structure(
  table_name varchar2(30) not null
 ,column_name varchar2(30) not null
 ,data_type varchar2(30)
 ,data_lenght number
 ,data_nullable varchar2(1)
 ,pk_seq number
 ,data_default varchar2(4000)
 )

Each block of source text should generate a set of SQL DML statements, and as you see, there are a number of conversions that have to be made. E.g:
char -> varchar
char* -> clob
double -> number
int -> number


I thougth about using awk for this, but my knowledge of scripting in general and awk in particular falls short. I would highly appreciate some pointers from any users of the forum. Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Comparing two files in UNIX and create a new file similar to equi join

I have 2 files namely branch.txt file & RXD.txt file as below Ex:Branch.txt ========================= B1,Branchname1,city,country B2,Branchname2,city,country B3,Branchname3,city,country B4,Branchname4,city,country B5,Branchname5,city,country RXD file : will... (11 Replies)
Discussion started by: satece
11 Replies

2. Shell Programming and Scripting

Using awk and sed to modify a create sql script

Hi, I have a file which contains the following data claim_src|clm_id,typ_id pat_src|pat_id prov_src|prov_id,clm_id,prov_name The first field is table name and second field is primary keys of the table Now I have three files which contain ddl of each table. clam_src.sql... (4 Replies)
Discussion started by: wahi80
4 Replies

3. Programming

[SQL] Insert content file to mysql

dear all, i want to insert string in file to mysql i just want how to do that cause i am poor in sql languages ... so this file like this DATA.txt doni|student|westjava|123412|lombok| iwan|student|westjava|1234412|utankayu| rio|student|westjava|12342|cempedak| so i want insert DATA.txt to... (2 Replies)
Discussion started by: zvtral
2 Replies

4. Shell Programming and Scripting

Wrap lines with awk to create SQL script

Greetings! Some of my files list hardware errors (we test electronic components), some have none. If the file name has no errors, I still want to display a message like "No error", else I display the error from the file itself. I came up with this (with help) for myfile in `find . -name... (2 Replies)
Discussion started by: alan
2 Replies

5. Shell Programming and Scripting

convert file into sql insert stmt

My file is now cleaned, sanitized & prepped: 07/07/2008 21:18:51 Installation 52016 complete *BUT NOTHING CHANGED* 07/21/2008 15:28:15 Removal 52016 complete 07/21/2008 15:34:15 Removal 55856 complete 12/08/2009 19:30:40 Installation 62323 complete 12/08/2009 19:39:06 Installation ... (6 Replies)
Discussion started by: dba_frog
6 Replies

6. Emergency UNIX and Linux Support

Insert data into sql queries from a file

Hello friends, I need to insert data from a file to another. I need this to form an sql query file which will consist of 50.000 INSERT INTO sentences. my sql query file will consist of 50.000 times the below line consecutively: insert into subscriber... (6 Replies)
Discussion started by: EAGL€
6 Replies

7. Shell Programming and Scripting

need to create a insert query for a file

Hi Guys, I need to create a insert query for the below file Fri Sep 4 06:25:51 2009 ACTION : 'CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1 ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)' DATABASE USER: '/' PRIVILEGE : SYSDBA CLIENT USER: oracle CLIENT TERMINAL: pts/3... (6 Replies)
Discussion started by: mac4rfree
6 Replies

8. UNIX for Dummies Questions & Answers

store SQL statements and results in a file

Hello Guys... I want a small help from you guys. Actually in Oracle, we are having a utlity called spool through which can store whatever SQL statements executed and other queries and the output of those queries in a file So, similarly in Unix, if I start a session executing a number of Unix... (2 Replies)
Discussion started by: mraghunandanan
2 Replies

9. Shell Programming and Scripting

create Insert script from a file

Hi , I have a text file text.txt which contains values as ULTRA,OTHERS,Mumbai,16912 ULTIMATE,OTHERS,Mumbai,16913 ULTIMATIUM,OTHERS,Mumbai,16914 I want to read the file line by line and create insert scripts like INSERT INTO TAB ( DESC,PLACE,NUMBER ) VALUES... (3 Replies)
Discussion started by: ultimatix
3 Replies

10. Shell Programming and Scripting

isql input file with multiple sql statements

I've got: isql -U $USERID -S $SERVER -D $DATABASE -i inputfile.sql -o outputfile.txt in inputfile I have: go sql#1 go sql#2 go sql#3 go I also tried without "go" and with";" instead which did not work SQL statements will work if I paste them directly into the script and use EOF ... (0 Replies)
Discussion started by: Cailet
0 Replies
Login or Register to Ask a Question