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