Connection between fact and source
Most of my data is imported from Ancestry.com and is therefore afflicted with all the census records being of type Residence. I am attempting to modify the Facts – Change Fact Type script so as to avoid changing legitimate Residence facts. My problem is that I cannot find how sources are related to facts. At this point, all I need is to know how the tables are related. Modifying the script I want to do myself, if I can.
In a certain sense, "sources" are not related to facts. Rather, "citations" are related to facts. I put the terms "sources" and "citations" in quotes because I think there is a certain fuzziness about what these terms actually mean. I will therefore discuss your question in terms of RM's tables and RM's user interface. I make note of the curiosity that RM's Facts from the user interface are stored in the EventTable and there is no FactTable.
RM has a CitationTable which is the key to your question. The contents of the CitationTable are visible in the RM user interface as "Source Details". This is the green area as opposed the yellow area on the Edit Source subscreen in the RM user interface. Rows in the CitationTable can be JOINed to people, families, events (facts), or alternate names. In your case, you will want to JOIN the CitationTable with the EventTable where all the facts are stored. To JOIN to the EventTable, you will need CitationTable.OwnerType = 2 because it's OwnerType = 2 that links from the CitationTable to the EventTable.
So your query will look something like
SELECT (some stuff)
FROM EventTable AS E
JOIN
CitationTable AS C on C.OwnerType = 2 AND C.OwnerID = E.EventID;
I did something like this - adding a census record for every residence record for a census. It was a pig and I messed up the database (a play copy) twice before I got it right. I relied on the fact that census residences had particular dates so only picked out the records with those dates. And since RM dates are a pain, I picked out records from my event table with those dates and used a select date from eventtable where eventid in (x,y,z) subquery to establish them.
I successfully created the new event and copied the citations for the residence events to new citations for the matching census event then realised I also needed the media links for the citations! So it became a three query process with the same CTEs re-used and more added.
Here's my SQL - it worked for my database setup but I don't guarantee it will work for all.