0

I want to get the difference of two sql dumps. I have to save the difference in a new file. The difference should be legitimate query! For example, If I have a file with following content,

CREATE TABLE IF NOT EXISTS `newtable` (
  `name` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `newtable` (`name`, `id`) VALUES
(1, 2);

and a second file with,

CREATE TABLE IF NOT EXISTS `newtable` (
  `name` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `newtable` (`name`, `id`) VALUES
(1, 2),
(4, 5);

Then I would get,

INSERT INTO `newtable` (`name`, `id`) VALUES
(1, 2),
(4, 5);

So how is this possible???

MJQ
  • 1,778
  • 6
  • 34
  • 60
  • 1
    There is no way to do both a diff on the two files, and keep only diffs, while maintaining proper queries. That defies the nature of a 'diff' as it will only show you the differences in the two files. You'll need to write an application that actually parses the two files, compares them and creates output based on those diffs. – Ian Mar 27 '13 at 14:01

1 Answers1

1

Here is one method, assuming that neither table has duplicates:

select (case when max(src) = 1 then 'DELETED'
              else 'ADDED'
         end) as what, name, id
from ((select 1 as src, name, id
       from newtable1 t
      ) union all
      (select 2 as src, name, id
       from newtable2 t
      )
     ) t
group by name, id
having count(*) = 1

This returns whether the row is added or deleted from one table to the other. You can insert these results into another table -- or wherever -- using an insert statement.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Are you expecting someone here to write you an application to do this for you? – Ian Mar 27 '13 at 13:59
  • I expect if someone has knowledge of some pre built script or code that does that! – MJQ Mar 27 '13 at 14:16