Source+Templates+-+Merge+Duplicates

RootsMagic has no Merge Source Templates function and it really needs one. When you copy multiple times from one database to another and there are citations involved that use the same **custom** source template, each time that source template is brought over it is treated as a new source template and the associated source is, too. So now you have multiple identical source templates with no tool to merge them. And you have multiple identical master sources which cannot be merged by AutoMerge because they use different source template records. You can merge them manually but that is painfully tedious given that the identical properties of the source templates should make it possible to automate it.

This script automatically merges **custom** source templates that have matching (case and space character insensitive 2016-04-22) : Variances in name, the sentence templates for Short Footnote and Bibliography and other fields are ignored which means that source templates that are not quite exact duplicates in all respects will be merged. Unfortunately, it is still sensitive to differences in Hints and Long Hints which are not critical to the merging of source templates.
 * footnote sentence templates
 * field definitions

The first source template (lowest TemplateID) in a set of duplicates will be the last one standing. All sources that once used the duplicates will now use it.

The script does leave a trail of two custom tables that could be cleared out with the addition of DROP TABLE statements added at the end or run separately, identical to the two already in the script.

Rev 2013-08-07 to correct bug. Rev 2016-04-22 to make tolerant of differences in case and spacing.

code format="sql" -- SourceTemplates-MergeDuplicates.sql /* 2013-07-30 Tom Holden ve3meo 2013-08-07 corrected error in Update SourceTable which obtained only the first TemplateID from xLookupSourceTemplateTable 2016-04-22 now tolerates differences in case and space characters in Footnote and FieldDefs

Merges custom source templates that have identical Footnote sentence templates and field definitions. Ignores differences in Short Footnotes, Bibliography and other fields. Makes the lowest TemplateID of a set of duplicates the master.

It is still sensitive to differences in hints in what would be otherwise identical field definitions that could be merged. - needs parsing to clear that out.

--Create a table of the master custom source templates having duplicate(s) DROP TABLE IF EXISTS xDupSourceTemplateTable

CREATE TABLE IF NOT EXISTS xDupSourceTemplateTable AS SELECT TemplateID , Name , Description , Favorite , Category , FootnoteCore , ShortFootnote , Bibliography , FieldDefsCore FROM ( SELECT COUNT-1 AS Dupes,  TemplateID , Name  , Description  , Favorite  , Category  , REPLACE(LOWER(Footnote),' ',) AS FootnoteCore  , ShortFootnote  , Bibliography  , REPLACE(LOWER(FieldDefs),' ',) AS FieldDefsCore FROM (SELECT * FROM SourceTemplateTable WHERE TemplateID > 999 ORDER BY TemplateID DESC) GROUP BY FootnoteCore, FieldDefsCore ) WHERE Dupes > 0

-- Create table of matching custom source templates DROP TABLE IF EXISTS xLookupSourceTemplateIDTable; CREATE TABLE IF NOT EXISTS xLookupSourceTemplateIDTable AS SELECT xD.TemplateID AS MasterID, ST.TemplateID FROM xDupSourceTemplateTable xD INNER JOIN SourceTemplateTable ST WHERE xD.FootnoteCore LIKE REPLACE(LOWER(ST.Footnote),' ','') AND xD.FieldDefsCore LIKE REPLACE(LOWER(ST.FieldDefs),' ','') AND ST.TemplateID > 999

-- Revise SourceTable to point to master TemplateID --EXPLAIN QUERY PLAN UPDATE SourceTable SET TemplateID = (SELECT MasterID FROM xLookupSourceTemplateIDTable xL WHERE SourceTable.TemplateID=xL.TemplateID) WHERE TemplateID IN (SELECT TemplateID FROM xLookupSourceTemplateIDTable)

-- Delete now unused duplicate Templates DELETE FROM SourceTemplateTable WHERE TemplateID IN ( SELECT TemplateID FROM xLookupSourceTemplateIDTable WHERE TemplateID != MasterID )

code