You haven't addressed any of the points that I raised in my response, so it still isn't clear exactly what you are trying to do.  Giving you a solution would...

The CREATE TABLE is in there to create the sample data.  PERIOD.  Since you would be using the live data, you don't need to create the sample data, so you...

No, because a complete solution would violate 1NF, which is not allowed in SQL Server.  (All records must have the same number of fields.)

Second, you haven't specified the...

It would help if you posted the query as well.  The SQL Plan does contain some of the query text, but it gets truncated.

Drew

A LEFT OUTER JOIN should have worked.  Can you post your query and we can figure out what you did wrong?

Drew

Reported as spam.

Reported as spam.

A 547 error is a Foreign Key Violation.  You're trying to insert a value in your table that references a record in another table, but the other table doesn't contain...

Please don't create multiple threads for the same topic.  It fragments the conversation.   https://www.sqlservercentral.com/Forums/1865681/Alter-datatype-in-multiple-colums

Generally INSERTs are done as a SET and the INSERT would succeed or fail as a whole.

If you just want to test your error handling, use a THROW...

It will never return any rows, because a record will always match itself, so the COUNT() will always be at least 1 (assuming that Tbl1.ID is not nullable).  Assuming that...

The standard way to return one row from a group based on a priority is to use ROW_NUMBER.

;
WITH CTE AS
(
    SELECT
        N.PARENTACCOUNT,
        n.TYPE,
        n.Street,

A foreign key is a link to a specific record in another table  (or another record in the same table).  Creating a foreign key field that is auto-incrementing would create...