Sed template does not give expected result

Question

I have a file with a lot of SQL INSERT constructs. I am trying to write a sed script to retrieve rows containing the name of an INSERT table.

   INSERT INTO Table1 values( val1, vale2, val3 );

    INSERT INTO Table2
    VALUES( val1, vale2, val3 );

    INSERT
    INTO
    Table3
    VALUES( val1, vale2, val3 );

    insert into table4
    SELECT col1 from
    table4
    where condition1 = condition2
    ;
    INSERT
    INTO
    table5 (col1, col2, col3)
    VALUES( val1, vale2, val3 );

    insert into table6 (col1,
    col2,
    col3, col4
    )
    SELECT col1, col2, col3,
    col4 FROM
    table6
    WHEREcondition1 = condition2
    ;

My conclusion should be:

 insert into table4
 insert into table6

This is what I tried. However, I am not sure why my sed templates do not work. A script is pickup lines that have the word SELECT. I am using GNU sed 4.2. Any help would be greatly appreciated.

# select all lines between INSERT and ; 
sed -n '/Insert/I,/;/ {
#delete the blank line
/^\s*$/{
i\ ...deleting blank line
=
d}
#selecting sub pattern range between INTO and SELECT
/into/I, /Select/I {
i\
...inside the Into---Select range
p
}
}' < testfile

My current output is above:

...inside the Into---Select range
INSERT INTO Table1 select values( val1, vale2, val3 );
 ...deleting blank line
120
...inside the Into---Select range
INSERT INTO Table2
...inside the Into---Select range
values( val1, vale2, val3 );
...inside the Into---Select range
INSERT
...inside the Into---Select range
 INTO
...inside the Into---Select range
Table3
...inside the Into---Select range
values( val1, vale2, val3 );
...inside the Into---Select range
insert into table4
...inside the Into---Select range
SELECT col1 from
...inside the Into---Select range
 INTO
...inside the Into---Select range
Table5 (col1, col2, col3)
...inside the Into---Select range
values( val1, vale2, val3 );
...inside the Into---Select range
insert into table6 (col1,
...inside the Into---Select range
col2,
...inside the Into---Select range
col3, col4
...inside the Into---Select range
)
...inside the Into---Select range
SELECT col1, col2, col3,

Answer 1

This may work for you (GNU sed):

sed -nr '/insert/I{:a;/;/!{$!{N;ba}};s/^\s*(insert\s+into\s+\S+)\s+(\([^)]*\))*\s*select.*/\l\1/i;T;s/\s+/ /gp}' file

seds -nr, grep- regexp. , insert ( ). , ;. (-), insert into tablename ... select, , . , .

Answer 2

$ sed -rn 'H;1h;/;/!d;x; s/.*insert[[:space:]]+into[[:space:]]+([[:alnum:]]+)[[:space:]]+([(][^)]*[)][[:space:]]+)?select.*/insert into \1/Ip' file
insert into table4
insert into table6

• -r

  sed .

• -n

  sed -, .

• H;1h;/;/!d;x;

  , .

  H;1h , . ;, ( d). , sed . x, , , ;. x , .

• s/.*insert[[:space:]]+into[[:space:]]+([[:alnum:]]+)[[:space:]]+([(][^)]*[)][[:space:]]+)?select.*/insert into \1/Ip

  , into select . select.

$ sed -rn '/insert/I,/;/{H;/;/!d;x; s/.*insert[[:space:]]+into[[:space:]]+([[:alnum:]]+)[[:space:]]+([(][^)]*[)][[:space:]]+)?select.*/insert into \1/Ip}' file
insert into table4
insert into table6

Answer 3

j.sed

#n
/INSERT/{
    :loop
    /;/!{
        N
        b loop      
    }
    /SELECT/{
        :l2
        s/\([^a]*able.\).*/\1/
        p
        b
    }
    /select/b l2
}
/insert/b loop

sed -f j.sed foo.txt

insert into table4
insert into table6

#n .

/INSERT/matches the insert and launches the branch with the name loop. Until we reach the semicolon, it adds the next line to the template space using N.

If the template space matches SELECTor SELECT, we go to a branch with a name l2that deletes everything after "insert into tablex". We print the line with pand go to the end of the script with b.

/insert/ b loopjust jumps to loopif it matches the lowercase "insert".