如何编辑巨大的SQL转储？
I have a huge SQL-Dump with a lot of statements like
CREATE TABLE ...
INSERT INTO ...
and so on.
I want to edit this file. At first I want to delete all "CREATE TABLE" statements with all attributes. I only want the "INSERT INTO" statements. Is there an easy way? I'm a rookie at this area.
All "INSERT INTO" statements look like this:
INSERT INTO 'xyz' VALUES (a, b, c, d), (e, f, g, h), (c,d,e,g) ...
I want to have it this way at the end:
INSERT INTO 'xyz' VALUES (<tenant_id>, a, b, c, d), (<tenant_id>, e, f, g, h), ...
Is there an easy way to realize that? E.g. with PHP or Regex? And how can I do this? Thank you!
If u have access any unix like system I recommend this:
sed '/CREATE TABLE */d' current.dump > new.dump
it will remove all the create table lines and pipe the rest to a new dump file
See example 10 "display the lines which does not matches all the given pattern" from http://www.thegeekstuff.com/2009/03/15-practical-unix-grep-command-examples/
Syntax:
grep -v -e "pattern" -e "pattern"
e..g.
grep -v -e "CREATE TABLE" current.dump > new.dump
The assumption is that the CREATE TABLE statement is on a single line.
For Windows you could use various ports of grep command, e.g. http://stefanstools.sourceforge.net/grepWin.html