SQL - How to alter table while doing INSERT INTO?

Question

I have 2 similar tables Table1 and Table2. I want to bring over some records from Table1 to Table2, for which I used the INSERT INTO statement. There is a column "EntityColumn" in Table1 with unique values like C1, C2, C3 etc.

For every record that is copied from Table1 to Table2, I want a column added to a third table - Table3 - and the name of the column should be the value of the EntityColumn field. Attached image should provide depict this.

Please tell me how this can be achieved.

Answer 1

You cannot readily do this.

The logically approach would be to write a trigger, and for each value being inserted, you would alter the third table and add a column.

However, a DML trigger cannot run an alter table statement to add a column, according to the documentation.