Error referencing new column in temporary 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

Old Forums

CLOSED - General SQL Server

Error referencing new column in temporary table

Author

Topic

SQLScapeGoat
Starting Member

3 Posts

Posted - 2004-02-05 : 09:45:31

I'm posting this in hopes that someone can figure out how to reference a new column in a temporary table that is built with sp_executesql. I am receiving the following error message "Invalid column name 'id'.". I can't display my actual code, but the following script is the same concept that I'm trying to achieve:
------------------------------------------
use pubs
go

declare @strSQL nvarchar(300)

set @strSQL = N'
select au_lname
,au_fname
into #temp
from authors

alter table #temp add au_NEWid int identity

select au_NEWid
from #temp'

exec sp_executesql @strSQL
----------------------------------------------

I've noticed that the new column auNEWid is added by the alter table statement since it appears in the result set if I do not reference the new column and simply select all columns:

-----------------------------------------------
use pubs
go

declare @strSQL nvarchar(300)

set @strSQL = N'
select au_lname
,au_fname
into #temp
from authors

alter table #temp add au_NEWid int identity

select *
from #temp'

exec sp_executesql @strSQL
--------------------------------------------------

Is there any way to only reference the new column au_NEWid?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-02-05 : 10:09:25

The results of an ALTER TABLE are generally only available at the next batch; you must issue a GO before referencing a new column explicitly.

Jonathan
Gaming will never be the same

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-05 : 15:19:18

Its true about using GO.

But I have tried a similar thing and battled to get it right.
The problem is that when GO is within the single quotes ie the text is in red in Query Analyzer then I seem to get an error saying something about unclosed quotation marks.

This baffled me because when I changed the exec statement to print, it printed the SQL statement fine.

I tried executing the example above with quotes too - Same Problem.

If someone can get it right, please let me know.
Regards,
Duane

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-05 : 16:00:38

OK, but why do you need to do this?


declare @strSQL nvarchar(300)

set @strSQL = 'select au_lname,au_fname into #temp from authors '
+'GO '
+'alter table #temp add au_NEWid int identity'
+' '
+'select * from #temp '
exec(@strSQL)

All just a matter of playing arounf with the syntax

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-05 : 16:05:00

Yes,
Thanks that works great.

My excuse is that it is 23:00 here in SA.

I'm going to bed now.

Good Night

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-05 : 16:07:59

Thats no excuse .. its 0240 here in India

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-05 : 16:10:20

quote:
Originally posted by The Enigma

Thats no excuse .. its 0240 here in India

You once told me to get a life....2:40?

Wow

Brett

8-)

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-05 : 16:14:20

And you are still not getting on my nerves .... :)

Yup ... going off to sleep at exactly 03:00

SQLScapeGoat
Starting Member

3 Posts

Posted - 2004-02-05 : 16:17:39

Thanks Brett. However, if you reference the new column in the last select, you still get the "Invalid column name 'au_NEWid'.":

use pubs
go
declare @strSQL nvarchar(300)

set @strSQL = 'select au_lname,au_fname into #temp from authors '
+'GO '
+'alter table #temp add au_NEWid int identity '
+' '
+'select au_NEWid from #temp where au_NEWid > 5'
exec(@strSQL)

Also, the first GO works, but a GO after the alter table statement causes an error.

I ended up creating a global temporary table so I could run some where clauses on it. It's cheesy, but it allows me to reference the new column added to the temp table.

I do appreciate the posts.

Cheers!

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-05 : 16:17:40

quote:
Originally posted by The Enigma

And you are still not getting on my nerves .... :)

Yup ... going off to sleep at exactly 03:00

And when do you get up?

Brett

8-)

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-05 : 16:19:23

That would be about 9:00 or 9:15 ... work place is nearby ... so get to the office by 10:00

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-05 : 18:15:04

I like mysteries but this one is discouraging....

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-06 : 14:47:07

declare @s nvarchar(300)
set @s='select * into #t from t '+
'alter table #t add A int identity '+
'select * from #t'
exec(@s)

In last select result you see column A.
Now replace it with B. Run it again.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 14:54:22

I don't get it ?? Why not just:

DECLARE @s varchar(300)
SET @s = 'SELECT identity(int,1,1) A, * INTO #t FROM t SELECT A FROM #t'
EXEC(@s)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-06 : 15:44:47

As to me I never suggest that a question poster is too stupid
or something of this kind. It's just intentional simplifications
for readers sake. I always hope it is.

SQLScapeGoat
Starting Member

3 Posts

Posted - 2004-02-07 : 12:09:15

Thanks ehorn! The "identity(int,1,1) A" column will work perfectly for my needs. This prevents me from having to alter the temp table in order to add an identity column and allows me to select the results I need with a filter on the new column.:

use pubs
go
declare @s nvarchar(300)

set @s = N'select au_lname, au_fname, identity(int,1,1) id into #temp from authors '
+N'select id from #temp where id > 5'
exec(@s)

Chaio!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources