I have a file that contains a series of SQL commands. These files are designed to create tables in a database, then the index, then any foreign keys. Essentially, what I want to do is capture the "create table" portion but leave off the rest once I get
to the end of the create section.

Imagine this file:

/* SAMPLE DATA */
create table students
(
stud_no serial not null ,
stud_dept_id nchar(3) not null ,
stud_name nchar(50) not null ,
stud_lgn_id nchar(8) not null ,
stud_last_lgn datetime year to second
);

create unique index stud_key1 on students
(stud_no,stud_dept_id) using btree ;

alter table students add constraint (foreign key
(stud_dept_id) references departments
constraint stud_dept_1);

/* END SAMPLE DATA */


I'm thinking this should be something I can use awk to accomplish, but I'm having a bit of trouble getting the logic right.

The idea was to set a variable X=0 in the BEGIN section. Then when I encounter the "create table" line, set X=1. As long as X=1, I can print out the lines. The moment I encounter the ");" to close the create statement, set X=0 or exit the awk statement.

My problem seems to be getting the correct syntax. I'm used to simple stuff like look for a line and print it. But doing something if X=1 does not seem to be working for me.

This logic seems to work:

cat file1 |
awk 'BEGIN {X=0}
/create table/ {X=1}
X==1 {print $1,$2,$3,$4,$5,$6}
/\)\;/ {X=0}'

But I'm not sure that awk is the best approach.

My questions:

1) Is awk recommended for this search and selective list of data, or is there a better way that someone could suggest?

2) Is there a way for me to ask it to display the entire line, not having to specify "print $1,$2,$3, and so on? I seem to remember having to change the field separator value or something. I'm more used to cut command where you can specify from "field X
until end of line".

Thanks for any advice

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

FYI - I think I figured it out. Through other posts about awk, I found out that there is an option to use the $0 variable which represents the entire line. So I can use this code:

awk 'BEGIN {X=0}
/create table/ {X=1}
X==1 {print $0}
/\)\;/ {X=0}'

It reads my input file until I reach the "create table" line but does not print anything because X is 0. When I see the create line, X becomes 1, at which point it starts printing out each line in the file. When I get to the ");" that completes the
create table SQL statement, x gets set to 0 again and stops printing any subsequent lines.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)