How to write a T-SQL Script for different Source DB/Table versions

Okay, this is going to be quite a messy answer, as there's a lot of things going on here to get this to work in a reasonably sensible way. I'm using MERGE with dynamic SQL to generate scripts then run them for each table.

First I need some test data, so I created a Temp database to play with:

USE Temp;
GO
CREATE SCHEMA destination;
GO
CREATE SCHEMA source1;
GO
CREATE SCHEMA source2
GO
CREATE TABLE destination.tableA (
    ID INT,
    Field1 VARCHAR(50),
    Field2 VARCHAR(50),
    Field3 VARCHAR(50));
GO
CREATE TABLE source1.tableA (
    ID INT,
    Field2 VARCHAR(50));
GO
CREATE TABLE source2.tableA (
    ID INT,
    Field1 VARCHAR(50),
    Field2 VARCHAR(50));
GO

Then I added some test data, making this repeatable:

DELETE FROM destination.tableA;
DELETE FROM source1.tableA;
DELETE FROM source2.tableA;
INSERT INTO source1.tableA SELECT 1, 'dog';
INSERT INTO source1.tableA SELECT 2, 'cat';
INSERT INTO source2.tableA SELECT 1, 'dog', 'harold';
INSERT INTO source2.tableA SELECT 3, 'mouse', 'midge';
GO

The plan is to start with a totally empty destination table, and to have some cases where all the data comes from one table, and other cases where there's a mixture, e.g. one table provides one piece of data, put there's another column for the same primary key in another table. This makes things much more complex, as now we need to INSERT or UPDATE depending on the data. This is where MERGE comes in.

I also use FOR XML PATH to get comma-separated lists. Here's the "nasty" dynamic SQL:

IF OBJECT_ID('tempdb..#schemas') IS NOT NULL
    DROP TABLE #schemas;
GO
SELECT SCHEMA_NAME INTO #schemas FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = 'Temp' AND SCHEMA_NAME LIKE 'source%';
WHILE EXISTS (SELECT * FROM #schemas)
BEGIN
    DECLARE @schema VARCHAR(50);
    SELECT TOP 1 @schema = SCHEMA_NAME FROM #schemas;
    SELECT @schema;
    DECLARE @sql NVARCHAR(4000);
    SELECT @sql = N'MERGE destination.tableA AS [target] USING (SELECT * FROM ' + QUOTENAME(@schema) + '.tableA) 
        AS [source] (' 
        + STUFF((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'Temp' AND TABLE_SCHEMA = @schema AND TABLE_NAME = 'TableA' ORDER BY ORDINAL_POSITION FOR XML PATH('')), 1, 1, '')
        + N') ON [target].ID = [source].ID
        WHEN MATCHED THEN UPDATE SET '
        + STUFF((SELECT ',' + COLUMN_NAME + ' = [source].' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'Temp' AND TABLE_SCHEMA = @schema AND TABLE_NAME = 'TableA' AND COLUMN_NAME != 'ID' ORDER BY ORDINAL_POSITION FOR XML PATH('')), 1, 1, '')
        + N' WHEN NOT MATCHED THEN INSERT ('
        + STUFF((SELECT ',' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'Temp' AND TABLE_SCHEMA = @schema AND TABLE_NAME = 'TableA' ORDER BY ORDINAL_POSITION FOR XML PATH('')), 1, 1, '')
        + N')
        VALUES ('
        + STUFF((SELECT ', [source].' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'Temp' AND TABLE_SCHEMA = @schema AND TABLE_NAME = 'TableA' ORDER BY ORDINAL_POSITION FOR XML PATH('')), 1, 1, '')
        + ');';
        EXEC sp_executesql @sql;
        SELECT @sql;
        DELETE FROM #schemas WHERE SCHEMA_NAME = @schema;
END;
GO
SELECT * FROM [destination].tableA;

Here's an example of one of the dynamic SQL scripts that gets run:

MERGE destination.tableA AS [target] USING (SELECT * FROM [source1].tableA) 
AS [source] (ID,Field2) ON [target].ID = [source].ID
WHEN MATCHED THEN UPDATE SET Field2 = [source].Field2 WHEN NOT MATCHED 
THEN INSERT (ID,Field2)
VALUES ( [source].ID, [source].Field2);

The end result of this is:

ID  Field1  Field2  Field3
1   dog     harold  NULL
2   NULL    cat     NULL
3   mouse   midge   NULL

Which looks good to me?

However, I imagine you would need to make quite a few changes to get this to work with your environment. I'm hoping this gives you a few ideas on how this could be done?