WHILE loop to replace data in temp table columns

Question

 Hello,

I have 2 temp tables

DECLARE @Jresults TABLE
(guest_title		varchar(10)		null,
 guest_initial		varchar(20)		null,
 guest_name			varchar(50)		null,
 email				VARCHAR(100)	null,			
 postcode			varchar(10)		null,
 gender				varchar(1)		null,
 comms				varchar(20)		null,
 intrst_name1		varchar(30)		null,
 intrst_name2		varchar(30)		null,
 intrst_name3		varchar(30)		null,
 intrst_name4		varchar(30)		null,
 intrst_name5		varchar(30)		null,
 intrst_name6		varchar(30)		null,
 intrst_name7		varchar(30)		null,
 intrst_name8		varchar(30)		null,
 intrst_name9		varchar(30)		null,
 intrst_name10		varchar(30)		null,
 intrst_name11		varchar(30)		null,
 intrst_name12		varchar(30)		null,
 intrst_name13		varchar(30)		null,
 intrst_name14		varchar(30)		null,
 intrst_name15		varchar(30)		null,
 intrst_name16		varchar(30)		null,
 intrst_name17		varchar(30)		null,
 intrst_name18		varchar(30)		null,
 intrst_name19		varchar(30)		null,
 intrst_name20		varchar(30)		null,
 intrst_name21		varchar(30)		null,
 intrst_name22		varchar(30)		null,
 intrst_name23		varchar(30)		null,
 intrst_name24		varchar(30)		null,
 intrst_name25		varchar(30)		null,
 intrst_name26		varchar(30)		null,
 intrst_name27		varchar(30)		null,
 intrst_name28		varchar(30)		null,
 intrst_name29		varchar(30)		null,
 intrst_name30		varchar(30)		null,
 intrst_name31		varchar(30)		null,
 intrst_name32		varchar(30)		null,
 intrst_name33		varchar(30)		null,
 intrst_name34		varchar(30)		null,
 intrst_name35		varchar(30)		null,
 intrst_name36		varchar(30)		null,
 intrst_name37		varchar(30)		null,
 intrst_name38		varchar(30)		null,
 intrst_name39		varchar(30)		null,
 intrst_name40		varchar(30)		null,
 arrival			datetime		null,
 departure			datetime		null,
 total_rate			numeric(10,2)	null,
 reservation_id		numeric(10,0)	null,
 gst_id				numeric(10,0)	null,
 comms_id			varchar(10)		null)




DECLARE @intrests TABLE
 (gst_id			numeric(10,0)	null,
  intrest_id		numeric(10,0)	null,
  intrest_name		varchar(30)		null)

the 1st has a lot of "intrst_name" columns . each of these needs to to havea value assigned to it.

currently, this is done using

	UPDATE @Jresults
	SET intrst_name33 = c.intrest_name
	from  @Jresults a, @intrests c
	where a.gst_id = c.gst_id and c.intrest_id=33

	UPDATE @Jresults
	SET intrst_name34 = c.intrest_name
	from  @Jresults a, @intrests c
	where a.gst_id = c.gst_id and c.intrest_id=34


etc

but I am now trying to make this more...compact with a WHILE loop , but I am getting different type of error message (IE, solve one "error" and another takes its place")

Currently , I have

DECLARE @Counter INT 
DECLARE @limit INT
DECLARE @ColName nvarchar(100)
declare @sql nvarchar (1000);
declare @tableName nvarchar (10);

SET @Counter=1
SET @limit=40
WHILE (@Counter <= @limit)
BEGIN
	SET @ColName='intrst_name'+(SELECT CONVERT(varchar(2),@Counter))
	-- Add intrests (no of intrest found in INTEREST table)
	set @sql = N'UPDATE @Jresults set ' + @ColName + '= c.intrest_name from @Jresults a, @intrests c
	where a.gst_id = c.gst_id and c.intrest_id='+(SELECT CONVERT(varchar(2),@Counter))+';'
	print @sql
	exec(@sql)
   SET @Counter  = @Counter  + 1
END

but it i get  the message

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Jresults".

the @sql looks like 

UPDATE @Jresults set intrst_name1= c.intrest_name from @Jresults a, @intrests c
	where a.gst_id = c.gst_id and c.intrest_id=1;

NB: number does change each time.

I have put the temp table names in []

	set @sql = N'UPDATE [@Jresults] set ' + @ColName + '= c.intrest_name from [@Jresults] a, [@intrests] c
	where a.gst_id = c.gst_id and c.intrest_id='+(SELECT CONVERT(varchar(2),@Counter))+';'

but then get

Msg 208, Level 16, State 1, Line 1
Invalid object name '@Jresults'.

Please can someone tell/show me what I need to to iterate though the loop  to change the intrst_name1,intrst_name2, etc till the limit is reached, that will use the temp tables for the data ?

Many Thanks in advance

Answer 1

You cannot use a table variable in that context.  You will need to change @Jresults to a temp table #Jresults.  That will fix your issue.

Answer 2

HI Tom, thanks for getting back so fast. Sorry to be a dummy, but, how would I do that  ? 

Answer 3

I think I understand what you mean now. 

create table #Jresults
(guest_title		varchar(10)		null,
 guest_initial		varchar(20)		null,
 guest_name			varchar(50)		null,

and 

create table  #glotmpintrests 
 (gst_id			numeric(10,0)	null,
  intrest_id


etc

and then 

	set @sql = N'UPDATE #glotmpJresults set ' + @ColName + '= c.intrest_name from #glotmpJresults  a, #glotmpintrests c
	where a.gst_id = c.gst_id and c.intrest_id='+(SELECT CONVERT(varchar(2),@Counter))+';'

THANK YOU