Iterate through the columns of a temp table

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 2000 Forums

Transact-SQL (2000)

Iterate through the columns of a temp table

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-15 : 14:20:35

is there a way to iterate through the columns of a temporary table?
The number of columns that exist in the temp table can change and I need to know the name of those columns as well.

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 14:26:51

U may use

• SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Mytable'
or
• Select NAME from SYSCOLUMNS Where ID In (Select ID From SYSOBJECTS Where Name = 'myTable')

Srinika

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-15 : 14:30:50

You could do the following:[CODE]select sc.*
from tempdb..sysobjects so inner join tempdb..syscolumns sc on sc.id = so.id
where so.name like '#temp1%'[/CODE]Where #temp1 is the name of your temp table

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-15 : 14:56:27

quote:
Originally posted by 00kevin

is there a way to iterate through the columns of a temporary table?
The number of columns that exist in the temp table can change and I need to know the name of those columns as well.

Thanks

As always, if the # of columns and/or their names in tables or resultsets (or even temp tables) varies, then you are most likely doing something wrong. Your *rows* should have varying data in them, but the columns themselves should remain constants. If you are trying to do crosstabs or something similiar in T-SQL, then I recommend doing these at the presentation layer.

- Jeff

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-15 : 15:14:49

yeah I agree but I have no choice but to get this working in sql. this is basically a email tempalte engine in sql :(

this is what I am forced to work with. :(
maybe you can provide some suggestions.. I'm trying to use cursors.

DECLARE cur CURSOR FOR
SELECT *
FROM #TEMP_EMAILDATA

OPEN cur;
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
BEGIN

declare col cursor for
select column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#TEMP_EMAILDATA'
open col
fetch next from col into @column_name
while @@FETCH_STATUS =0
begin
--????

#TEMP_EMAILDATA

set @msg = replace(@msg,'#' + @column_name '#',@column_value)
fetch next from col into @column_name
end
close col
deallocate col

exec spSendMail @email_to,@reply_email,@subject,@msg

FETCH NEXT FROM cur
END
CLOSE cur
DEALLOCATE cur

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-15 : 15:52:13

It is hard to tell what you are trying to do here, looks like some sort of find/replace where you are taking the contents of @msg and doing a search/replace based on the columns and their values passed. As always, it is easier to help if you explain carefully what you need to do instead of simply showing us code that doesn't work without further explaination.

If my guess is somewhat correct, then you should have a table with 1 row per find/replace pair (with two constant named columns) and simply loop through the *rows* and not the columns to do your find/replace. You are working with unnormalized data, which requires all kinds of temp tables and cursors and dynamic sql; if you have a normalized schema, you simply loop through rows in your table.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-15 : 15:53:14

There has to be a better way!
Post some sample data of your #TEMP_EMAILDATA table!

rockmoose

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-15 : 16:44:42

@jsmith - sorry for not providing a description. You are correct the procedure does a find and replace on the msg looking for fields that are contained in the email using the syntax #column_name#

I think I may have figured it out. just have to test it

ALTER PROCEDURE [dbo].[spSendEmailTemplate]
(
@email_template_id int,
@sql varchar(1000)
)
as

declare @email_to as varchar(100)
declare @from_name as varchar(100)
declare @column_name as varchar(100)
declare @column_value as varchar(200)
declare @reply_email as varchar(100)
declare @subject as varchar(100)
declare @msg as varchar(8000)
declare @msg_tpl as varchar(8000)

create table #COL_VAL (column_value varchar(200));

set @sql = replace(@sql, 'from', 'into #TEMP_EMAILDATA from')
exec(@sql)

select
@from_name = from_name,
@reply_email = reply_email,
@subject = subject,
@msg = msg,
@msg_tpl = msg

from EMAIL_TEMPLATE
where email_template_id = @email_template_id

declare cur cursor for
select
email
from #TEMP_EMAILDATA

open cur;
fetch NEXT from cur into @email_to
while @@FETCH_STATUS = 0
begin

declare col cursor for
select column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#TEMP_EMAILDATA'
open col
fetch next from col into @column_name
while @@FETCH_STATUS =0
begin

exec('insert into #COL_VAL (column_value) select top 1 ' + @column_name + ' as column_value from #TEMP_EMAILDATA where email=''' + @email_to + '''')
select @column_value = column_value from #COL_VAL
set @msg = replace(@msg,'#' + @column_name + '#',@column_value)
truncate table #COL_VAL

fetch next from col into @column_name
end
close col
deallocate col

print @msg
exec spSendMail @email_to,@reply_email,@subject,@msg
set msg=msg_tpl

fetch NEXT from cur into
@email_to
end
close cur
deallocate cur

drop table #TEMP_EMAILDATA
drop table #COL_VAL

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-15 : 16:53:58

this will most likely be rather slow :(

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-15 : 19:23:37

It sure will be slow, and ugly, and hard to maintain. I will again suggest using a normalization table (or two -- you may need 1 table for the email itself and another related table for all of the "fields" that you will find/replace).

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-15 : 19:40:51

Getting back to the original question of how to find the column names in a temp table:


select
	*
from
	tempdb.information_schema.columns
where
	object_id('tempdb..#temp') = object_id('tempdb..'+TABLE_NAME)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources