Use column names as parameter in Insert into from an existing table

Question

I want to copy data from one table into another existing table. For this I am using following query:

insert into table_A(col1, col2, .....)
select col1, col2, .....
from table_B

But, I want these column list come from an existing table or view. The table contains a column col_name which contains all column names.

I want something which is given below:

insert into table_A(select col_name from y)
select (select col_name from y)
from table_B

Answer 1

What you ask for requires dynamic SQL: that is, build a query string, then execute it. Assuming that your version of SQL Server supports string_agg():

declare @lst nvarchar(max), @sql nvarchar(max);
select @lst = string_agg(col_name, ', ') within group(order by col_name) from y;
set @sql = N'insert into table_a (' + @lst + N') select ' + @lst + N' from table_b';
EXEC sp_executesql @sql;

Demo on DB Fiddle:

create table table_a(id int, val int);
create table table_b(id int, val int);
create table y(col_name varchar(10));

insert into y values('id'), ('val');
insert into table_b values(1, 2), (3, 4);

declare @lst nvarchar(max), @sql nvarchar(max);
select @lst = string_agg(col_name, ', ') within group(order by col_name) from y;
set @sql = N'insert into table_a (' + @lst + N') select ' + @lst + N' from table_b';
EXEC sp_executesql @sql;

select * from table_b;

id | val
-: | --:
 1 |   2
 3 |   4