Begin...commit every 50 rows

Question

I have a 500,000 line sql script.

update users set region_id = 9814746 where id = 101 and region_id is null;
update users set region_id = 9814731 where id = 102 and region_id is null;
update users set region_id = 3470676 where id = 103 and region_id is null;
...
....

I need to be able to generate a script with begin...commit every 50 lines. How do I do this? I'll be running this in pg-admin.

Thanks, Sharadov

what kind of programming languages can you use? – reto Aug 20 '09 at 17:40 — reto, Aug 20 '09 at 17:40

score 5 · Accepted Answer · answered Aug 20 '09 at 17:52

5

This might be a good start:
cat sql.file | perl -e 'i=0;print "begin;\n"; while(<>){print;i++;if(i % 50 == 0){print "end;\nbegin;\n";}}print "end\n";' > outputsql.file

answered Aug 20 '09 at 17:52

chotchki

4,258
5
34
55

I have no perl skills whatsoever. – sharadov Aug 20 '09 at 18:01
You don't need perl skills. Just use the code that chotchki provided. – Aug 20 '09 at 18:02

score 3 · Answer 2 · answered Aug 20 '09 at 18:01

3

Simple awk solution:

cat your_sql_file | awk '
    BEGIN{print "BEGIN;"}
    {print}
    (0 == NR%50) {print "COMMIT;\nBEGIN;"}
    END{print "COMMIT;"}'

answered Aug 20 '09 at 18:01

Worked like a charm! Now understand the power of the shell. – sharadov Aug 20 '09 at 18:38
1

for PROGRAM in sed awk grep cut cat tac nl; do man $PROGRAM; done; while true; do play_with_it; done – Aug 20 '09 at 18:44

Begin...commit every 50 rows

2 Answers2

Linked