Insert into command with Columns

Question

Possible Duplicate:
SQL Server: How do you return the column names from a table?

I wanted to know if there was a way to insert the Column names from on table into a field in another table.

I have a new table where the column names of the old table is now a parameter in the new table.

ie.

old table
[id] [name] [name2] [name3]
  1    x      x       x

new table
[id] [name type]
 1    name
 2    name2
 3    name3    

Answer 1

The following query will return the columns of a specified table:

SELECT COL.COLUMN_NAME
FROM
    information_schema.columns COL
WHERE 
    (COL.TABLE_SCHEMA = 'schema_name') AND
    (CO.TABLE_NAME = 'table_name')

You can use the result set and insert it into a temporary table, or use it for whatever you need.

Note: I ran it on SQL Server 2008, but it should work on 2005 as well.

Answer 2

INSERT INTO NewTable
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'OldTable'

Answer 3

You can SELECT INTO a new table or a temp table. Example below is a temp table:

SELECT COLUMN_NAME
into #temp
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'yourSchema' AND
    TABLE_NAME = 'yourTable'

select *
from #temp

drop table #temp