ALTER temp table - unexpected behaviour

Funny little problem with a temp table created by an sp and then
altered within the scope of the same sp. It doesn't seem to work when I
try it.
Here's some sample code;
/**** start create code ***/
use northwind
create procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp
GO
/**** end code ***/
If this is executed you get;
ProductName RowID
--
Alice Mutton 1
Aniseed Syrup 2
Boston Crab Meat 3
Camembert Pierrot 4
Carnavon Tigers 5
Now try this;
/**** start alter code ***/
use northwind
alter procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp where RowID = 1 --added where clause
GO
/**** end code ***/
When executed you get;
Result:
Error "Invalid column name 'RowID'"
So RowID is returned as part of the result set but if you try to add a
where clause on it, it doesn't exist? The same is true for inserts (if
the column being added is not an identity column).
I worked around the problem by creating the temp table with the
identity column first and then doing an insert but I'm curious about
the error. What did I miss?
Thanks.This is by design. At compile time, the engine has to validate the existence
of the columns. At this time, the alter statement is not yet committed,
thus, the error on compilation for the select statement.
You should change your proc to include the identity column as part of your
select/into.
e.g.
create procedure dbo.uspTest as
select top 5 productname,RowID=identity(int,1,1)
into #myTemp
from products
order by productname
select * from #myTemp
where RowID = 1 --added where clause
go
-oj
"Wolf" <spamcatcher5050@.hotmail.com> wrote in message
news:1124249380.861639.272680@.g14g2000cwa.googlegroups.com...
> Funny little problem with a temp table created by an sp and then
> altered within the scope of the same sp. It doesn't seem to work when I
> try it.
> Here's some sample code;
>
> /**** start create code ***/
> use northwind
> create procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp
> GO
> /**** end code ***/
> If this is executed you get;
> ProductName RowID
> --
> Alice Mutton 1
> Aniseed Syrup 2
> Boston Crab Meat 3
> Camembert Pierrot 4
> Carnavon Tigers 5
> Now try this;
> /**** start alter code ***/
> use northwind
> alter procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp where RowID = 1 --added where clause
> GO
> /**** end code ***/
> When executed you get;
> Result:
> Error "Invalid column name 'RowID'"
> So RowID is returned as part of the result set but if you try to add a
> where clause on it, it doesn't exist? The same is true for inserts (if
> the column being added is not an identity column).
> I worked around the problem by creating the temp table with the
> identity column first and then doing an insert but I'm curious about
> the error. What did I miss?
> Thanks.
>|||Awesome. This is going to save me so much time.
Thanks oj.|||You should note that the ORDER BY clause may effectively be ignored by the
server. In particular there is no guarantee that the IDENTITY values will be
assigned in Productname order. Don't use ORDER BY on SELECT INTO or
INSERT... SELECT.
David Portas
SQL Server MVP
--

ALTER TABLE Question

I have a temp table I'm filling from two different data sources. I then do
an ALTER TABLE statement to add an identity column. So far so good. But when
try to select the first x number of records using the identity column to put
into a cursor I get a 'Invalid Column Name' error. Anyone give me a clue?
TIA...just curious: if you need the identity column, why not just include it
in the create table for the temp table?
if you want a good answer, you'll have to post DDL, code, sample data,
desired results, etc. otherwise you'll just get guesses
http://www.aspfaq.com/5006
glen wrote:
> I have a temp table I'm filling from two different data sources. I then do
> an ALTER TABLE statement to add an identity column. So far so good. But wh
en
> try to select the first x number of records using the identity column to p
ut
> into a cursor I get a 'Invalid Column Name' error. Anyone give me a clue?
> TIA...
>|||glen wrote:

> I have a temp table I'm filling from two different data sources. I then do
> an ALTER TABLE statement to add an identity column. So far so good. But wh
en
> try to select the first x number of records using the identity column to p
ut
> into a cursor I get a 'Invalid Column Name' error. Anyone give me a clue?
> TIA...
ALTER TABLE is unwise in a proc. You'll likely get errors because the
server is unable to resolve all the column names at compile-time.
The "obvious" solution is to include the IDENTITY column when you
create the table instead of adding it later. Is that a problem?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ordinarily I would, but because of the different data sources I need to
number the rows after the data is in and sorted.
Here's my code:
ALTER PROCEDURE mediaq.usp_VideoKeywordCombo2
(
@.Page int,
@.RecsPerPage int,
@.keyword VARCHAR(200),
@.termlist VARCHAR(200)
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
sdate DATETIME,
id INT,
offset INT,
description VARCHAR(50),
thumbnail_name VARCHAR(250),
video_proxy VARCHAR(250),
ip_address VARCHAR(20),
tn_url VARCHAR(50),
mms_url VARCHAR(50),
start_time DATETIME,
sample VARCHAR(250),
time_in INT,
cc_time_in INT,
char_offset INT,
JPG_IMG VARCHAR(100),
VID_ASF VARCHAR(250),
vid_path VARCHAR(250),
cc_text VARCHAR(250),
logo_path VARCHAR(100),
content_date DATETIME,
content_source VARCHAR(100),
content_title VARCHAR(250),
content_id BIGINT,
journalist VARCHAR(200),
moname VARCHAR(100),
content_summary VARCHAR(2000),
article_url VARCHAR(250),
date_inserted DATETIME
)
-- vars for cursor
DECLARE @.bid INT
DECLARE @.id INT
DECLARE @.offset INT
DECLARE @.description VARCHAR(50)
DECLARE @.thumbnail_name VARCHAR(250)
DECLARE @.video_proxy VARCHAR(250)
DECLARE @.ip_address VARCHAR(20)
DECLARE @.tn_url VARCHAR(50)
DECLARE @.mms_url VARCHAR(50)
DECLARE @.start_time DATETIME
DECLARE @.sample VARCHAR(250)
DECLARE @.time_in INT
DECLARE @.cc_time_in INT
DECLARE @.char_offset INT
DECLARE @.JPG_IMG VARCHAR(100)
DECLARE @.VID_ASF VARCHAR(250)
DECLARE @.vid_path VARCHAR(250)
DECLARE @.P1 INT
DECLARE @.P2 INT
DECLARE @.cc_text VARCHAR(250)
DECLARE @.logo_path VARCHAR(100)
DECLARE @.counter INT
DECLARE @.edate DATETIME
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems
(content_date,content_source,content_tit
le,content_id,
journalist,moname,content_summary,articl
e_url,date_inserted)
EXEC mq..usp_KeywordSearch_ft_KV @.keyword
-- Insert records from keyword search proc into temp table
INSERT INTO #TempItems (id,offset,description,thumbnail_name,
video_proxy,ip_address,tn_url,mms_url,st
art_time,sample)
EXEC usp_SearchVideo_IX @.keyword
UPDATE #TempItems SET sdate = start_time where not start_time IS NULL
UPDATE #TempItems SET sdate = date_inserted where not content_date IS NULL
CREATE index glen on #TempItems (sdate desc)
ALTER TABLE #TempItems ADD bid int identity
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
-- Use Cursor to generate new field data based on returned data
DECLARE tmpCursor CURSOR FOR
SELECT id, offset, description, thumbnail_name, video_proxy, ip_address,
tn_url, mms_url,
start_time, sample, bid FROM #TempItems WHERE bid > @.FirstRec AND bid <
@.LastRec
AND start_time IS NOT NULL
OPEN tmpCursor
Fetch next from tmpCursor
INTO @.bid, @.id,
@.offset,@.description,@.thumbnail_name,@.vi
deo_proxy,@.ip_address,@.tn_url,
@.mms_url,@.start_time,@.sample
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.char_offset = mediaq.uf_get_search_position(@.id,@.termlist)
SET @.cc_time_in = dbo.GetThumbnailTimeCC(@.id,@.char_offset)
SET @.time_in = dbo.GetThumbnailTimeTN(@.id,@.char_offset)
IF @.cc_time_in > 10000
SET @.cc_time_in = @.cc_time_in - 10000
ELSE
SET @.cc_time_in = 0
SET @.video_proxy = right(@.video_proxy,(len(@.video_proxy) -
(patindex(@.video_proxy,'/asf/') - 4)))
IF RIGHT(LTRIM(RTRIM(@.thumbnail_name)),1) = '/'
SET @.JPG_IMG = 'http://' + @.tn_url + '/JPGS/' + @.thumbnail_name +
LEFT(@.thumbnail_name,LEN(@.thumbnail_name
) - 1) + '_' +
CONVERT(VARCHAR(20),@.time_in) + '.JPG'
ELSE
SET @.JPG_IMG = 'http://' + @.tn_url + '/JPGS/' + @.thumbnail_name + '_' +
CONVERT(VARCHAR(20),@.time_in) + '.JPG'
SET @.P1 = PATINDEX('%\ASF\%',@.video_proxy)
SET @.VID_ASF = RIGHT(@.video_proxy,LEN(@.video_proxy) - @.P1 - 4)
SET @.vid_path = 'http://mediaq.enr-corp.com/dis_vid_fee.asp?tin=' +
CAST(@.cc_time_in AS VARCHAR(30)) + '&ip=' + @.mms_url + '&bn=' + @.VID_ASF
IF @.char_offset < 40
set @.P2 = 40
ELSE
set @.P2 = @.char_offset - 40
SET @.cc_text = (SELECT SUBSTRING(cc,@.P2,100) as cc_text FROM videos WHERE
id = @.id)
SET @.cc_text = REPLACE(@.cc_text,CHAR(13),' ')
SET @.logo_path = 'http://mediaq.enr-corp.com/channels/' + LEFT(@.VID_ASF,4)
+ '.jpg'
Update #tempitems set cc_time_in = @.cc_time_in, time_in = @.time_in,
char_offset = @.char_offset,
JPG_IMG = @.JPG_IMG, vid_asf = @.VID_ASF, vid_path = @.vid_path,
cc_text = @.cc_text, logo_path = @.logo_path where bid = @.bid
Fetch next from tmpCursor
INTO @.bid, @.id,
@.offset,@.description,@.thumbnail_name,@.vi
deo_proxy,@.ip_address,@.tn_url,
@.mms_url,@.start_time,@.sample
END
CLOSE tmpCursor
DEALLOCATE tmpCursor
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.bid >= @.LastRec
)
FROM #TempItems
WHERE bid > @.FirstRec AND bid < @.LastRec
SET NOCOUNT OFF
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%23epBGh4GGHA.3856@.TK2MSFTNGP12.phx.gbl...
> just curious: if you need the identity column, why not just include it in
> the create table for the temp table?
> if you want a good answer, you'll have to post DDL, code, sample data,
> desired results, etc. otherwise you'll just get guesses
> http://www.aspfaq.com/5006
>
> glen wrote:|||> Ordinarily I would, but because of the different data sources I need to
> number the rows after the data is in and sorted.
Why do you think adding an IDENTITY column will guarantee that the identity
values are applied exactly as the #temp table is allegedly "sorted"?
A|||I had assumed that the adding the Identity column after adding and indexing
the data would give me what I wanted. I'm using the identity column for
paging.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOcStq4GGHA.3176@.TK2MSFTNGP12.phx.gbl...
> Why do you think adding an IDENTITY column will guarantee that the
> identity values are applied exactly as the #temp table is allegedly
> "sorted"?
> A
>|||basically, I want to have an int column numbered after the the data is in
and indexed, for paging purposes.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137518046.399755.210350@.g47g2000cwa.googlegroups.com...
> glen wrote:
>
> ALTER TABLE is unwise in a proc. You'll likely get errors because the
> server is unable to resolve all the column names at compile-time.
> The "obvious" solution is to include the IDENTITY column when you
> create the table instead of adding it later. Is that a problem?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||>I had assumed that the adding the Identity column after adding and indexing
>the data would give me what I wanted.
Well, this is a bad assumption. IDENTITY is not guaranteed to work this
way.

> I'm using the identity column for paging.
Stop. Read:
http://www.aspfaq.com/2120|||....and therefore I should use?...
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eAN4iW5GGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Well, this is a bad assumption. IDENTITY is not guaranteed to work this
> way.
>
> Stop. Read:
> http://www.aspfaq.com/2120
>|||Did you read the article?
"glen" <gsault@.enr-corp.com> wrote in message
news:emDuza5GGHA.2320@.TK2MSFTNGP11.phx.gbl...
> ...and therefore I should use?...sql

ALTER TABLE MyT ALTER COLUMN IdtyCol t_idty NOT NULL Identity

Hello World,
Is there a way not to drop and create the table with temp table to alter a
column as in the subject?
Thanks,
C TO
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
Ehm, as far as I know, the "subject" should work just fine.
Is there an error that you're getting? If so, what is it?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||YOu have to recreate the column on order to create a identity column:
http://www.windowsitpro.com/Article...2080/22080.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"C TO" <CTO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2193C5FD-CC98-417A-90EC-751F71128B19@.microsoft.com...
> Hello World,
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
> Thanks,
> C TO|||
a
> Ehm, as far as I know, the "subject" should work just fine.
> Is there an error that you're getting? If so, what is it?
Woops, mixed this up with "not null".
Bugger.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

alter table #TempTable problem

I am trying to add a column to a temp table and then immeditaely query
against that new column. If I do this in Query Analyzer it works fine
as long as there is a go in between, but I can't use a go inside a
stored proc.. How do i get SQL to finish processing the alter table
command so I can use the new column?

alter table #TempPaging add TIId int not null identity
--go --fixes the problem in QA, but not in proc
Select * From #TempPaging Where TIId > 0 AND TIId < 11

(Error TIId does not exist)pb648174 (google@.webpaul.net) writes:
> I am trying to add a column to a temp table and then immeditaely query
> against that new column. If I do this in Query Analyzer it works fine
> as long as there is a go in between, but I can't use a go inside a
> stored proc.. How do i get SQL to finish processing the alter table
> command so I can use the new column?
> alter table #TempPaging add TIId int not null identity
> --go --fixes the problem in QA, but not in proc
> Select * From #TempPaging Where TIId > 0 AND TIId < 11

It would help if you provided the context you are trying to do
this in. The problem is that if the procedure is recompiled after
CREATE TABLE, but before ALTER TABLE, the refernce to the column
not yet created causes an error, as there is - luckily! - no deferred
name resolution on column names.

Thus some kind of workaround is needed, which is why I asked for
context.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Why add a column to the temp table? Just create it with the right columns to
start with.

--
David Portas
SQL Server MVP
--|||Ok, here is what I am trying to do - make a generic routine for paging
that can be added to any proc and can be maintained easily rather than
copying and pasting in the same code all the time.

In proc 1 I have:

Select *
Into #TempPaging
from ScheduleTask

--do paging based on temp table
exec spDoPaging @.itemsPerPage, @.Page, @.TotalPages OUTPUT, @.TotalRecords
OUTPUT

In proc 2(spDoPaging I have:
Select @.TotalRecords=Count(*) From #TempPaging
Select @.TotalPages=CEILING(Cast(@.TotalRecords as
decimal)/Cast(@.itemsPerPage as decimal))

-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.itemsPerPage
SELECT @.LastRec = (@.Page * @.itemsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT * FROM #TempPaging WHERE TIID > @.FirstRec AND TIID < @.LastRec

--clean up
drop Table #TempPaging

The kicker is that it all depends on the identity column so that I have
a row number for each item in the list, so that I can get the
appropriate page. so the below line is crucial

--add numbering
alter table #TempPaging add TIId int not null identity

Adding this line to proc1 works and is my workaround for now, but I
would like to have all the paging functionality in the spDoPaging if
possible and just use the "Into #TempPaging" and call to stored proc
anytime I want to add paging to a particular proc.|||You can put the IDENTITY function in the SELECT INTO statement, then
you don't have to add it later.

There are also other paging methods that don't require IDENTITY and
temp tables:
http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--|||> You can put the IDENTITY function in the SELECT INTO statement, then
> you don't have to add it later.

Example:

SELECT IDENTITY(INT, 1,1) AS tiid, *
INTO #TempPaging
FROM YourTable

--
David Portas
SQL Server MVP
--|||pb648174 (google@.webpaul.net) writes:
> Ok, here is what I am trying to do - make a generic routine for paging
> that can be added to any proc and can be maintained easily rather than
> copying and pasting in the same code all the time.

> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT * FROM #TempPaging WHERE TIID > @.FirstRec AND TIID < @.LastRec

You could put this in dynamic SQL with sp_executesql.

However, I think you are in a dead end here. I would assume that
you expect the IDENTITY values to respect some sort of order that you
want the data to be presented in. Don't count on that.

The SELECT INTO with the IDENTITY() function suggested by David is a
somewhat better bet if the amount of data is small, and you use an
ORDER BY clause. But you are not guaranteed to get rows in order.

CREATE TABLE followed by INSERT is safer, not the least if you add
OPTION (MAXDOP 1) to avoid surprises with parallelism.

Of course, since you don't know exactly what the table would look
like, CREATE TABLE is kind of difficult, but a SELECT INTO with
an IDENTITY() and a WHERE condition of 1 = 0 could cut it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||,IDENTITY(INT, 1,1) AS TIId --add numbering
Into #TempPaging

Ok, I changed my code to have this as the line(s) that I can copy and
paste into procs and then just put in the paging proc lines below that

--do paging based on temp table
exec spDoPaging @.itemsPerPage, @.Page, @.TotalPages OUTPUT,
@.TotalRecords OUTPUT

Which seems to work pretty well. Thanks for the help guys.

Alter Table

Simple SQL question.

I am trying to add multiple columns to a temp table and the alter statement throws the following error.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

The alter statement looks like this.

ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)

I thought you can add multiple columns putting the names in ( ).

Any ideas where I am doing wrong.

Thanks.

Quote:

Originally Posted by ymk

Simple SQL question.

I am trying to add multiple columns to a temp table and the alter statement throws the following error.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

The alter statement looks like this.

ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)

I thought you can add multiple columns putting the names in ( ).

Any ideas where I am doing wrong.

Thanks.

hi ymk,
remove the brackets after add. just
alter table #billingdata add t2 float,t3 varchar(20) null