1

I have a dump file with various INSERT statements. The file looks something like

start_lines

INSERT INTO table1234 …
INSERT INTO table1234 …
INSERT INTO table1234 …

INSERT INTO table7890 …
INSERT INTO table7890 …
INSERT INTO table7890 …

 end_lines

I need all the INSERT INTO table7890 statements to go above the INSERT INTO table1234 statements. I would like to keep all the start_lines and end_lines in the same position they were before the switch occurs. So, for instance, if the start_lines occupy lines 3 through 18, after switching the insert statements, they should continue to appear 3 through 18. Ditto for the end lines.

How do I switch the groups of statements ?

  • Bash is not a standard text editor; there are text processing tools to do that job, like perl, awk, sed, etc. – Jeff Schaller Dec 30 '16 at 3:21
  • Make sure your sample data is representative -- one line per insert, only two table names? – Jeff Schaller Dec 30 '16 at 3:22
  • Maybe you need to provide some more details on what you want to do, because as the question is expressed right now a single answer could be use tac file command (reverse read the file, opposite to cat) – George Vasiliou Dec 30 '16 at 4:47
  • Yeah good call. I added some clarification. I want to switch those INSERT INTO statements in the file, but I want all the other lines in the file to remain right where they are. – Dave Dec 30 '16 at 16:04
  • The more details you add, the more it seems like you want a script that will do things based on some obscure (possibly unneeded) logic that is known only to your brain. Your specs can not be applied as you seem to expect and you'll always get another case and another case where your mind will say "oh and I'd prefer if it did this instead of that on those lines." – Julie Pelletier Dec 30 '16 at 16:58
1

The most simplistic approach is to read the file twice, 1st time you save the paragraphs and 2nd time your swap them e.g. with awk (in paragraph mode):

awk -v RS= -v ORS='\n\n' 'NR==FNR{
if (/table1234/) {one=$0} else if (/table7890/){two=$0}
next
}
{
if (/table1234/) {$0=two} else if (/table7890/){$0=one}
}
1' infile infile

This will print a trailing empty line, if it matters...


Another way with ol' ed which does work in this particular case:

ed -s infile <<\IN
g/INSERT INTO table1234/d
-1r !grep -F 'INSERT INTO table7890' infile
+1,$g/INSERT INTO table7890/d
-1r !grep -F 'INSERT INTO table1234' infile
,p
q
IN
  • Your first solution caused a "awk(12770,0x7fffc50f43c0) malloc: *** mach_vm_map(size=18446744071562969088) failed (error code=3)" error but the second solution ran like a dream. – Dave Dec 30 '16 at 21:35
0

There are multiple ways to do it but the simplest I can see is with grep:

grep -v table1234 test.sql; grep table1234 test.sql

You start by outputting everything except table1234 lines, and after you only output table1234 lines.

If you want, you can also redirect it to a new file:

(grep -v table1234 test.sql; grep table1234 test.sql) > test2.sql
  • How does this move the lines "INSERT INTO table7890" above the "INSERT INTO table1234" lines? Your line, "grep -v table1234 test.sql; grep table1234 test.sql" doesn't reference "table7890". – Dave Dec 30 '16 at 16:01
  • @Dave: It makes all table1234 operations go at the end. It probably doesn't match your latest update which makes very little sense to me since the inserts are totally independent from each other. How can you expect independent things to take the same amount of space? – Julie Pelletier Dec 30 '16 at 16:55
  • I originally thought you had a good reason like constraints that depended on other tables to be populated before table1234 but if that's not your issue, you should really think it through. – Julie Pelletier Dec 30 '16 at 17:00
  • 1
    It is indeed to avoid foreign key errors. I need the "INSERT INTO table7890" statements to go above the "INSERT INTO table1234" statements to avoid foreign key errors. But if the "INSERT INTO table7890" go directly to the end of the ifle it will cause foreign key errors with the lines that occur after the "INSERT INTO table7890" statements. – Dave Dec 30 '16 at 17:05
  • 1
    Did you even try running my command to see if it would solve your problem or not? If it still has an issue, mention what the issue is precisely. – Julie Pelletier Dec 30 '16 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.