Creating a Complex Insert Statement

Question

So I’ve been trying to create an Insert statement for a report I am making, that draws information from several other tables, Columns and concatenates some of the columns.

So far I have this and it returns 0 rows with no errors.

INSERT INTO ClientNexusCleanseReport (PotentialMatchName,
                                      EstablishmentType,
                                      CorrectAllocationId,
                                      PotentialAddress,
                                      PotentialPostCode,
                                      ClosureDate,
                                      Company,
                                      CompanyCode,
                                      CRMName,
                                      HowItWasAllocated,
                                      CorrectClientNexusID,
                                      LastTransactionDate,
                                      HowItShouldBeAllocated,
                                      AllocationIDType,
                                      DataCategory,
                                      OtherComments)
SELECT et.EstablishmentName,
       et.[TypeOfEstablishment (name)],
       et.URN,
       CONCAT(et.street, ', ', et.locality, ', ', et.address3, ', ', et.Town, ', ', et.[county (name)]),
       et.postCode,
       et.CloseDate,
       ca.company,
       ca.code,
       ca.ClientName,
       CONCAT(ca.ClientKnownType, ' - ', cnm.ClientnexusSubType),
       cnm.ClientNexusMasterID,
       CONCAT(cp.year, cp.Month),
       'Edubase',
       'Edubase URN',
       '1a 1c',
       'Incorrect Name, Incorrect URN'
FROM edubasealldata et
     INNER JOIN ClientNexusMaster cnm ON cnm.ClientNexusName = et.EstablishmentName
     INNER JOIN ClientNexusCRMAccounts ca ON ca.ClientName = cnm.ClientNexusName
     INNER JOIN ClientNexusCRMProduct cp ON cp.Code = ca.Code
WHERE ca.ClientName = ' Ark Schools T/a Ark Priory Primary Academy';

The idea is to create a row of information on each Case I have to clean, i.e gather information on the Case from around the Database, manipulate that information (By using Concat) and compile it into a row that tells me the problem with that case as well as the correct information.

So I have gone through, added the INSERT Into statement, then added the SELECT statement then JOINED the Tables then specified the particular case I want this all to be about in a WHERE statement.

Problem is that I don't receive an error when I run this, but it says (0 rows affect). So I am not entirely sure what I have done wrong.

UPDATE

Thank you for all the advice and responses. I think I figured out where I was going wrong, as @Kendle said, my joins were not finding matching records.

Basically I am Data Cleaning.

My job is to find messy records (' Ark Schools T/a Ark Priory Primary Academy' and Match them to the correct record 'Ark Priory Primary Academy'. Then populate that record with the correct data and what the original problem was.

So I gather the incorrect data/business data from 3 of the tables, the Account Table, Product Table and the Master Table and then merge that with the correct data from the Edubase Table (I also fill in the gaps manually at the very end).

So the problem is that I don't have anything to directly link the Incorrect Data to the correct Data.

I have looked up on StackOverflow. Could I achieve the desired outcome with a CROSS JOIN where I specify to get in incorrect data WHERE ca.clientName = ' Ark Schools T/a Ark Priory Primary Academy' and correct data WHERE et.establishmentname = 'Ark Priory Primary Academy'?

Or could I use an INSERT statement followed by an UPDATE statement to produce result I want? SO INSERT... WHERE ca.clientName = ' Ark Schools T/a Ark Priory Primary Academy' then UPDATE ... WHERE et.establishmentname = 'Ark Priory Primary Academy'?

Thank you again for all of the help!