Editing a 9gb .sql file

Question

Hi. I've got a "slightly" large sql script saved as a textfile. It totals in at 8.92gb, so it's a bit of a beast.

I've got to do some search and replaces in this file(specifically, change all NOT NULL to NULL, so all fields are nullable) and then execute the darned thing. Does anyone have any suggestions for a text editor that would be capable of this?

The other way that I can see to solve the problem is to write a program that reads a chunk, does a replace on the stuff I need, and then save it to a new file, but I'd rather use some standard way of doing this.

It also does not solve the problem of opening the beast up in sql server management studio to execute the darned thing...

Any ideas?

Thanks, Eric

Answer 1

You can use an editor that maps files instead of loading them into memory. Like UltraEdit or Notepad++, I'm sure there are many more.

This is still slow but very functional.

Answer 2

sed is built for exactly that kind of job.

sed -e 's/\( NOT\)\? NULL/ NOT NULL/g' < input.sql > output.sql

sed is also available on Windows.

Edit: I modified my statement to avoid producing NOT NOT NULL when the input already contains NOT NULL.

Answer 3

Use sed, or simply perl -pne 's/foo/bar/' file.sql > newfile.sql (foo will be replaced with bar).

For loading SQL, use osql.exe that should be somewhere under c:\program files...\sql server\bin

Answer 4

You should consider using perl. It does an in-place text replacement. Use it as

perl -pie "s/ NOT NULL/ NULL/g" hugefile.sql

Answer 5

Careful with that axe Eugene...you don't want to clobber any WHERE clauses that should remain NOT NULL