CODEWITHSUNDEEP
sqlsql-server-2008
zom-pro
zom-pro

Reputation: 1649

use query output as column name in sql sever

My knowledge in SQL server 2008 is limited and I couldn't find exactly how to do this. My idea is to use the result of a SELECT procedure as a list of column names (based on the procedure created in this old question How do you return the 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 to fill up a bigger table with data from a table that has less columns (all of them are contained in the bigger table). Maybe there is a better way to do this.

Upvotes: 5

Views: 5489

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

You can't do this, this way. You have to use dynamic SQL to do this, 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);

This is assuming that all the list of columns that found in new_table, are alos found in the second table YourTableName otherwise, you will get an error saying that the column name is not found.

See it in action here:

Upvotes: 4

Related Questions