Insert a column name into a table

Question

I am trying to insert column names from one table as data into another in SQL Server. This is the query I'm using:

insert into TableB (Name) select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME = 'TableA' 

This returns null rows. I tried without specifying TABLE_NAME, and also tried specifying TABLE_SCHEMA. They both produce results, but not what I'm looking for.

Answer 1

I do not see any problems with the SQL code that you provided.

Other troubleshooting methods:

1) If you request:

 select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME = 'TableA' 

Are there any results?

2) If you try this INSERT, does it work?

 INSERT INTO Table_1 (Name) Select 'Name1' 

3) If you select these columns, do you see the TABLE_NAME list in the list?

 Select DISTINCT(TABLE_NAME) FROM INFORMATION_SCHEMA.columns 

---

Your SQL code worked for me with Microsoft's AdventureWorks-DB ( Table_1 is an empty table with a column name):

 INSERT INTO dbo.Table_1 (Name) Select COLUMN_NAME FROM INFORMATION_SCHEMA.columns where TABLE_NAME = 'ProductInventory' select * from table_1 

Output:

 ProductID LocationID Shelf Bin Quantity rowguid ModifiedDate 

Answer 2

Your query should work, as far as I can tell, I always use sys.columns and sys.tables for these queries, try:

 SELECT c.name ColumnName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'TableA'