OUTPUT is this possible. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OUTPUT is this possible.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-12-30 : 14:23:27

I was looking for a clever way to OUTPUT the column names of a query into a tmp table

something like


Declare @T table (ID int)

insert Into @T
select 1 Union all
select 2

Select a.ID,b.aColumnName
OUTPUT Into #Tmp --All the column names from the query
from
@T a
inner join 
T_MYTABLE b
on a.ID = b.ID

Obviously this isn't working code, but in a nut shell is there anyway to get the column names from a query into a tmp table w/o creating a table then selecting from INFORMATION_SCHEMA (Or other system tables)??

I'm almost positive this is not possible, but never hurts to ask.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:30:12

didnt get what you're asking. do you just want the column names of resultset as values to other table? or do you just want to create blank table with column of query?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-12-30 : 15:06:42

Basically

I would like to have a single statement that would output the column names into a table.

So if I had a table (MYNEWTABLE) that had columns A B C D E, I could run a query that would do this.

Create table #TMPTABLE(CName Char(1))

Select *
OUTPUT COLUMNNAMES INTO #TMPTABLE --ALL THE COLUMN
From
MyNewTable

Select * from #TMPTABLE

--RESULTS

CNAME
------
A
B
C
D
E

As stated this is likley not possible withought creating a view or table to for the Query itself then just query the INFORMATION_SCHEMA for the columns that belong to it, but I was hoping there was someway to utilize a output command in a select statement to somehow export the column names for the data being returned into a #TMPTABLE.

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-30 : 15:55:04

Would this work? Seems simple enough unless I miss the point.
Your temp table is created automagically.

select COLUMN_NAME into MyTempTable
from INFORMATION_SCHEMA.COLUMNS
where table_name ='My_Old_Table_Name'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:51:49

i would have also done in way revdnrdy had posted. Any reason why you cant have this approach Vinnie?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-12-31 : 03:12:09

The reason why it was not ideal is because there are joins to more than 1 table (I tried to illustrate this with the original post). I have used that method referanced by rev and am very familiar with it (that is why I specifically stated that I was looking for a alternative to that method in my posts).

I simply wanted to see if there was a alternative that would allow me to create the #TMP table with the column names w/o needing to first isolate the recordset into a table/view. It does not appear that there is a way to.

I appriciate your time, and Thanks for your help.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 03:38:12

SELECT TOP 0 *
INTO #tmp
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON ...

SELECT * FROM tempdb.information_schema.columns where table_name like '#Tmp_%'

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-31 : 03:43:54

Hi Peso, select top 0 from table means what Happens in the query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:01:55