5

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.

Community
  • 1
  • 1
zom-pro
  • 1,571
  • 2
  • 16
  • 32

1 Answers1

4

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:

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • I forgot to specify that the tables are based in different databases. Is there something I should take into consideration because of this condition? – zom-pro May 28 '13 at 12:59
  • @JorgePM For the `INSERT` statement use a fully qualified table name in the form of `DatabaseName.SchemaName.TableName` and for the script set `USE databasename;` to get teh columns' names for the first table. – Mahmoud Gamal May 28 '13 at 13:04