how to create table script

[patrickman]

Hi,

I need help , I have a file which lot of other entities like below, i want to create for each entity a sepearate create table script like below, Any help is much appreciated.

EMP EMP_NO NUMBER(10),
EMP ENAME VARCHAR2(10),
EMP SAL NUMBER(10)
DEPT DEPTNO VARCHAR2(10)
DEPT DNAME VARCHAR2(10)

create table EMP
(
EMP_NO NUMBER(10),
ENAME VARCHAR2(10),
SAL NUMBER(10)
)
create table DEPT
(
DEPTNO VARCHAR2(10)
DNAME VARCHAR2(10)
)

 

[PHV]

What have you tried so far and where in your code are you stuck ?
BTW, in your file I'd replace this:
DEPT DEPTNO VARCHAR2(10)
with this:
DEPT DEPTNO VARCHAR2(10)[!],[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[patrickman]

I don't know much, but i tried with the following. It is giving wrong.

cat aa|nawk -F"\t" '{prev="";if ($1 == prev) print "CREATE TABLE "$1 ; else print $2" "$3};prev=$1'

 

[PHV]

A starting point:
nawk '$1!=prev{if(NR>1)print ");";print "CREATE TABLE "$1"(";prev=$1}{print $2,$3}END{print ");"}' aa

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[patrickman]

You are just wonderful, It worked. if you can explain a bit that will be more helpful. Again thank U very much.

 

[patrickman]

Hi,

Is there is any way to take out last comma from the output.

create table EMP
(
EMP_NO NUMBER(10),
ENAME VARCHAR2(10),
SAL NUMBER(10),
)
See here SAL column at the end their is comma, is it possible to take out that comma. appreicate your help.

 

[PHV]

nawk -F'[ \t,]+' '$1!=prev{if(NR>1)print ");";print "CREATE TABLE "$1"(";prev=$1;c=" "}{print c$2"\t"$3;c=","}END{print ");"}' aa

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[patrickman]

Cool, thank you very much. It worked. Only thing comma is at the beginning instead of at the end. that's ok. i can create script. Thanks.