Remove insert data of a specific table from mysqldump output (sed?)

Question

I have a huge mysqldump output and want to exclude the inserts for a specific table.

The file looks like this:

--
-- Dumping data for table `big_table`
--

INSERT INTO `big_table` ...
INSERT INTO `big_table` ...


--
-- Table structure for table `next_table`
--

How can I cut out those inserts that come between "Dumping data for table big_table" and the next "Table structure for table" The file is too large to fit in a text editor.

Answer 1

I overlooked the fact that all the inserts of course start with the table name. So I can simply use

grep -v "INSERT INTO \`big_table\`" dump.sql > dump_stripped.sql

Answer 2

One solution using sed. It searches for all lines between literals -- Dumping data for table 'big_table' and -- Table structure for table. And comment those lines that don't begin with --.

Assuming content of infile:

1
2
3
4
--
-- Dumping data for table `big_table`
--

INSERT INTO `big_table` ...
INSERT INTO `big_table` ...


--
-- Table structure for table `next_table`
--
1
2
3
4
5
6

Run command:

sed -e '
    /-- Dumping data for table `big_table`/,/-- Table structure for table/ { 
        /^--/! s/^/--/ 
    }
' infile

With following output:

1
2
3
4
--
-- Dumping data for table `big_table`
--
--
--INSERT INTO `big_table` ...
--INSERT INTO `big_table` ...
--
--
--
-- Table structure for table `next_table`
--
1
2
3
4
5
6