Copy Fact to Group #update #namedgroup #events

Ever wanted to copy the same fact to a bunch of other people in your database? See the RootsMagic Forum thread Globally add fact to group for some background and an alternative method using GEDCOM. Here’s the braveheart method. See Copy RIN to REFN for a special variant of this query and a procedure that is applicable to both.

CopyFact2Group.sql – rev 2020-05-16 corrected LabelID to LabelValue for the GroupID

-- CopyFact2Group.sql
-- Tom Holden 5 Apr 2011
-- rev 2020-05-16 corrected LabelID to LabelValue for the GroupID
-- ALWAYS BACK UP YOUR DATABASE BEFORE RUNNING A QUERY THAT MODIFIES IT
-- Copies a fact from one person to all persons in a named group
--  - Media is NOT copied but Sources and Note are.
--  - Edit date is not modified from that of the original fact.
-- Requires GroupID and EventID values to be found and entered, each into
--  two queries. The GroupID is the LabelValue of the LabelName corresponding
--  to the name of the Group in the LabelTable table.
--  The EventID is easily found if it is the last fact entered - the EventID
--  of the last row in the table EventTable.
--
-- The first query adds a record to the EventTable for each person with
--  the same fact values as the record copied.
-- The second query adds an identical record to the CitationTable for each source for the
--  copied fact for each added fact. If n sources for the original fact, then the same n
--  sources for the fact are added to each person in the group.
--
-- This query copies the fact/event but not the Sources to the persons in the Named Group
-- CHANGE values of GroupID and EventID to your values
INSERT OR ROLLBACK INTO EventTable (EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate,
       IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)
  SELECT EventType, OwnerType, N.OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate,
       IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note
   FROM (SELECT N.OwnerID FROM NameTable N, GroupTable WHERE N.OwnerID >=StartID AND N.OwnerID <= EndID AND GroupID=???) N,
       EventTable E WHERE EventID=???;
 
-- This query copies the Sources for the newly created facts.
-- CHANGE value of C.OwnerID to your EventID value and GroupID to your GroupID
INSERT OR ROLLBACK INTO CitationTable (OwnerType, SourceID, OwnerID, Quality, IsPrivate, Comments, ActualText, RefNumber, Flags, FIELDS)
  SELECT C.OwnerType, C.SourceID, E.EventID, C.Quality, C.IsPrivate, C.Comments, C.ActualText, C.RefNumber, C.Flags, C.FIELDS
    FROM CitationTable C,
     (SELECT EventID FROM EventTable
        WHERE EventID >
         (SELECT MAX(EventID) FROM EventTable) - (SELECT SUM(EndID-StartID+1) FROM GroupTable
           WHERE GroupID=???)) E
    WHERE C.OwnerID=??? AND C.OwnerType=2;