awk while loop question 1

[BRADDBA]

I am an awk newbie. I have only doen one liners.
I am confused with the while loop and printf
output for program below.

I wish to automate a data refresh in a Development environment INSERT statements. My propose awk routine will
read text file of table, column and column sequence.

The imput data is:

Table Column Column Sequence

ACCOUNT_MASTER CHANNEL_ID 1
ACCOUNT_MASTER ACCOUNT_ID 2
ACCOUNT_MASTER LAST_UPDATE 3
PRODUCT PRODUCT_CODE 1
PRODUCT PRODUCT_COST 2
REGION REGION_ID 1
REGION ZIP 2

The desired output for each table is:

INSERT INTO TABLE_NAME
(COL1,
COL2,
COL3)
SELECT
COL1,
COL2,
COL3
FROM TABLE_NAME@PROUCTION;
COMMIT;

 

[futurelet]

Try this:

[tt]
$1 { if ($1!=table)
{ output()
table=$1
}
cols[$3]=$2
}
END { output() }

function output()
{ if (table)
{ out = "("
print "INSERT INTO", table
for (i=1;i<9;i++)
if (i in cols)
out = out cols ",\n"
print substr(out,1,length(out)-2) ")"
print "SELECT"
print substr(out,2,length(out)-3)
print "FROM " table "@PRODUCTION;"
print "COMMIT;"
}
# Wipe out array.
split("",cols)
}
[/tt]
If you have nawk, use it instead of awk because
on some systems awk is very old and lacks many useful features.

Let me know whether or not this helps.

 

[futurelet]

Put the program in a file named, say, "datarefresh.awk" and run it with [tt]nawk -f datarefresh.awk infile >outfile[/tt].

 

[BRADDBA]

futurelet,

The script works great

Thanks!

u r a god.

Please clarify a few things.

I understand the general flow and how and why.

How is the table_name in the second line suppressed
from printing when grabbing the second column name of the table group? How did first occurence get established?

Also, what line suppressed the printing of the comma ',' in the last column?

Thanks

 

[futurelet]

I added some comments.


# If $1 is empty (because line read is blank), this won't
# be executed.
$1 {
if ($1!=table)
{ # We've started reading a new table, as shown
# by the fact that the table name has changed.
output()
table=$1
}
# If the line we just read is
# "ACCOUNT_MASTER CHANNEL_ID 1", then
# the line below does [tt]cols[1]=["CHANNEL_ID"][/tt].
# $2 is the second field (or word), $3 is the 3rd.
cols[$3]=$2
}
END { output() }

function output()
{ if (table)
{ out = "("
print "INSERT INTO", table
# Build a string containing several lines.
for (i=1;i<9;i++)
if (i in cols)
out = out cols ",\n"
# We chop off the ",\n" at the end of the string
# using substr(). The line-breaks embedded in
# the string will make this print as more than
# one line.
print substr(out,1,length(out)-2) ")"
print "SELECT"
# Remove "(" at beginning and ",\n" at end.
print substr(out,2,length(out)-3)
print "FROM " table "@PRODUCTION;"
print "COMMIT;"
}
# Wipe out array so we have a clean slate for next
# table name.
split("",cols)
}

 

[BRADDBA]

futurelet,

Thanks for clarifying.

I was mis-understandingg the awk internal processing during the scans.

I thought the for loop was working one line and writing.

I now understand you/it was building a string with multiple lines.