Tuesday, March 15, 2022

[SOLVED] grep and awk to copy only insert statements

Issue

I have a .sql file that contains multi-lines Create, Insert, Alter Statements now I want to fetch only INSERT statements and copy that in another .sql file.

Sample for Create and Insert statements are

CREATE CACHED TABLE "PUBLIC"."xyz"(
    "ID" BIGINT NOT NULL,
    "GID" BIGINT NOT NULL,
    "GDATA" BINARY(65536)
);


INSERT INTO "PUBLIC"."INFORMIX_SERVERS" VALUES
(4521215141212121, 8763121524544545454
4545, X'3a000000127265706f7369746f7279536572');

I have tried the below commands with no success cat dump.sql | grep -i 'INSERT* );'

cat dump.sql | grep -E 'insert*);' | awk -F\> '{ print $2 }' | awk -F\< '{ print $1 }'


Solution

In case your Input_file's INSERT statements are having empty before and after its all lines then this could help.

awk -v RS= '/^INSERT/' Input_file

OR more generic solution you could try following.

awk '!NF||/^CREATE/{foundInsert=""} /^INSERT/{foundInsert=1} foundInsert' Input_file

Explanation: Adding detailed explanation for above.

awk '               ##Starting awk program from here.
!NF || /^CREATE/{   ##Checking condition if NF is NULL OR line starts from CREATE then do following.
  foundInsert=""    ##Nullifying foundInsert here.
}
/^INSERT/{          ##Checking condition if line starts from INSERT then do following.
  foundInsert=1     ##Setting foundInsert to 1 here.
}
foundInsert         ##Checking if foundInsert is SET then print that line.
' Input_file        ##Mentioning Input_file name here.


Answered By - RavinderSingh13
Answer Checked By - Mildred Charles (WPSolving Admin)
at