Re: Q: How to break SQL file into single SQL statements?

Dmitry Korenkov <korenkov@pluto.xTech.RU> wrote: > have an SQL script file with a bunch of SQL >statements separated by ';'. I have a problem >of breaking this file info single SQL statements >because ':' can be within SQL statement as well. >Does anyone know a program that can break SQL >file into single SQL statements? > >=Dmitry ><korenkov@pluto.iis.nsk.su> >:q Dmitry, Use awk or perl. Create a new file every time you run across the semi-colon UNLESS you are inside the definition of a stored procedure or a trigger (might be a few other other similar situations, but I'm too lazy to go to the syntax manual and figure them out). do it like this: sed -e 's/;/;\\n/' filename.sql | awk ' BEGIN { in_sps=0; in_trs=0 } #not in a stored proc or trigger stmt in_sps==0 && $1=="CREATE" && $2=="PROCEDURE" { in_sps=1 } #in stored proc in_trs==0 && $1=="CREATE" && $2=="TRIGGER" { in_trs=1 } #in trigger { print } #print every line- guaranteed to work because we add a newline #after every semi-colon with the sed command above. Sneaky. in_sps==1 && $1=="END" && $2=="PROCEDURE" { in_sps=0 } #out of stored proc in_sps==1 && $1=="END" && $2=="TRIGGER" { in_trs=0 } #out of trigger # if not in stored procedure or trigger or (add other conditions here) # then, print a line telling you to start another file. Or use the # appropriate awk syntax to create the file yourself: in_sps==0 && in_trs==0 { print; print "===========NEXT FILE============" } ' As you can see, I copped out on the awk code to create a new file, but what's there should be enough to get you started. Good luck with your project! ------------------------------------------------------- "You're missing the point," said the fork to the spoon.