Use query output as column name in sql sever

Question

My knowledge in SQL Server 2008 is limited, and I could not determine exactly how to do this. My idea is to use the result of the SELECT procedure as a list of column names (based on the procedure created in this old question How do you return column names from a table? ) I would like to do something like:

INSERT INTO new_table (SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName') SELECT (SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName') FROM YourTableName 

What I'm trying to do is populate a large table with data from a table with fewer columns (they are all contained in a large table). There may be a better way to do this.

Answer 1

You cannot do this. For this you need to use dynamic SQL, something like this:

 DECLARE @NewTablecols AS NVARCHAR(MAX); DECLARE @query AS NVARCHAR(MAX); select @NewTablecols = STUFF((SELECT distinct ',' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'new_table' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') , 1, 1, ''); SET @query = 'INSERT INTO new_table ( '+ @NewTablecols + ' ) SELECT ' + @NewTablecols + ' FROM YourTableName'; execute(@query); 

It is assumed that the entire list of columns found in new_table will be found in the second table YourTableName , otherwise you will receive an error message saying that the column name was not found.

Look here:

• SQL Fiddle Demo