merging two tables with some duplicate data

[galorin]

I have written a perl script that outputs a SQL script. I need to modify the outputted SQL so that if there is a duplicate Company Name, their info dumped somewhere for an audit.

One of my current dumped lines looks like this:

INSERT INTO companies (Company, Profession, Town, County, Postcode, Country, Telephone, Fax, `Email Address`, Website, `Rep Override`) VALUES ('!!!! TEST BUILDER', 'Builder', 'b', '', '', 'England', '01234 555666', '01234 666777', 'me@here.com', '', 'John Smith');

How can I modify this outputted line so that if there is another "!!!! TEST BUILDER" but with a different address, it dumps the above info into a seperate table, so I can manually compare the two entries and decide which one is correct?

 

[TonyGroves]

I suppose you would modify your Perl script to keep track (using a hash or whatever) of what company values have already been output, and if any record's company value has already been processed, use the alternative table name.

That might cater for duplicate values, but what about triplicates, or quadruplicates, and so on? Maybe a better solution would be to just use one table but alter the company value to indicate that is a duplicate, for example with a suffix like [1] or [2] and so on.

 

[galorin]

As I am running it, there is an existing table with data in it, and then the above SQL code inserts into the table. I am not very good with PERL or PHP as yet..

So is it not possible then to set company name as a key, and then do something like

INSERT INTO companies (Company, Profession, Town, County, Postcode, Country, Telephone, Fax, `Email Address`, Website, `Rep Override`) VALUES ('!!!! TEST BUILDER', 'Builder', 'b', '', '', 'England', '01234 555666', '01234 666777', 'me@here.com', '', 'John Smith') 
ON DUPLICATE KEY INSERT INTO alttable VALUES ('!!!! TEST BUILDER', 'Builder', 'b', '', '', 'England', '01234 555666', '01234 666777', 'me@here.com', '', 'John Smith')

 

[TonyGroves]

No, that code is not possible. You would need to know in advance if the key exists. Possibly the simplest way would be to test for an existing record just before composing your SQL for each record.

 

[galorin]

rats, PERL here I come