Sql dynamic table / dynamic inserts

magedfawzy · #1 (**permalink**) 09-25-2009

I have a file that reads File (X.txt)

Contents of record 1:
rdrDESTINATION_ADDRESS (String) "91 971502573813"
rdrDESTINATION_IMSI (String) "000000000000000"
rdrORIGINATING_ADDRESS (String) "d0 movies"
rdrORIGINATING_IMSI (String) "000000000000000"
rdrTRAFFIC_EVENT_TIME (String) "09090801212416"
rdrTRAFFIC_EVENT_TYPE (Long) 8

File (Y.txt)

Contents of record 2:
rdrDESTINATION_ADDRESS (String) "91 9715023423813"
rdrDESTINATION_IMSI (String) "00000002340000"
rdrORIGINATING_ADDRESS (String) "d0 etisalat"
rdrORIGINATING_IMSI (String) "000000000000000"

I want to create a dynamic table / insert of values into this table..

Code:

tmpdir=/tmp/records ; mkdir -p $tmpdir ;
tmpfile=/tmp/records/dump.$$
TABLE_COLUMNS=$tmpdir/columns.$$
TABLE_NAME=DUMP_$$
sqlload=$tmpdir/records/sqldump.sql


cat X.txt Y.txt| grep -i rdr | sort -ru  > $TABLE_COLUMNS 


echo "CREATE TABLE $TABLE_NAME  " > $tmpdir/create_dump_tbl.sql
while read line ; do
echo "$line VARCHAR2(50 BYTE)," >> $tmpdir/create_dump_tbl.sql
TABLE_COL=`echo $TABLE_COL "," $line`
done < $TABLE_COLUMNS


## above has a flaw ..for last line of " , " but anyway, not a big deal

## My Real issue is create an insert dynamically"
for field in `cat X.txt Y.txt`
do
echo "Insert into $TABLE_NAME ($TABLE_COL) VALUES $ " >> $sqlload
done

Note that File X.txt has 6 columns
and File Y.txt has 4 columns,
so how do i construct the statement, putting nulls in the 2 columns that are empty. (i.e. rdrTRAFFIC_EVENT_TIME rdrTRAFFIC_EVENT_TYPE )

Note: the columns from the File (x.txt & y.txt) do not appear in order

Thanks

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Dynamic SQl in KSH	kousikan	Shell Programming and Scripting	2	03-18-2007 12:12 PM
how to dynamic DNS	bondoq	Linux	1	11-14-2006 10:34 AM
Dynamic DNS	[MA]Flying_Meat	OS X (Apple)	0	12-06-2005 09:07 PM
Dynamic routing table	jdsnbr	AIX	1	05-06-2005 04:39 PM
dynamic pid?	yls177	UNIX for Dummies Questions & Answers	2	12-17-2002 08:26 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by