SP Indetity problem - SQL Server Forums

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

SQL Server Development (2000)

SP Indetity problem

Author

Topic

infrontweb
Starting Member

13 Posts

Posted - 2003-07-22 : 12:08:34

I am using the following SP to return 1 random record from a table:
---------------------------------
CREATE PROCEDURE sp_GetRandomAd
AS

declare @nRecordCount int
declare @nRandNum int

-- Create a temporary table with the same structure of
-- the table we want to select a random record from
CREATE TABLE #tmp_ads
(
ad_Image varchar(50),
ad_Link varchar(100),
ad_Alt varchar(100),
ad_ID int identity(1,1)
)

-- Dump the contents of the table to seach into the
-- temp. table
INSERT INTO #tmp_ads
Select ad_ID, ad_Image, ad_Link, ad_Alt From ads

-- Get the number of records in our temp table
Select @nRecordCount = count(*) From #tmp_ads

-- Select a random number between 1 and the number
-- of records in our table
Select @nRandNum = Round(((@nRecordCount - 2) * Rand() + 1), 0)

-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select ad_ID, ad_Image, ad_Link, ad_Alt From #tmp_ads
Where ad_ID = @nRandNum
GO
---------------------------------
I am getting the folowing error:
An explicit value for the identity column in table '#tmp_ads' can only be specified when a column list is used and IDENTIY_INSERT is ON.

Any suggestions?
Eric

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-22 : 12:19:55

Where is IDENTITY_INSERT set ON?

and why are you inserting into a temp table with an identity?

Aren't you getting the values from someplace else?

Take out the identity, or don't insert into it?

Unless it has a puprose then by all means.

Just set in the sproc...but I don't understand (damn I hat when the happens, esp so frequently)

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-07-23 : 07:21:37

If you are trying to return a rowset containing a single row from ads selected randomly, try:


select top 1
    ad_id,
    ad_image,
    ad_link,
    ad_alt
from
    ads
order by
    newid()

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 15:03:04

To answer your specific question, look at your code:

quote:

CREATE TABLE #tmp_ads
(
ad_Image varchar(50),
ad_Link varchar(100),
ad_Alt varchar(100),
ad_ID int identity(1,1)
)

-- Dump the contents of the table to seach into the
-- temp. table
INSERT INTO #tmp_ads
Select ad_ID, ad_Image, ad_Link, ad_Alt From ads

you have defined 4 columns in your temp table. the last one is an identity.

in your INSERT statement, you are selecting 4 columns from a table.

So, column 1 from select goes into column 1 in your temp table, col2 into col2, etc.

Thus, column 4 goes into your identity column .... hopefully, you can see that makes no sense.

i *think* what you want is:

INSERT INTO #tmp_ads (ad_Image, ad_Link, ad_Alt)
Select ~~ad_ID,~~ ad_Image, ad_Link, ad_Alt From ads

note three things:

1) ad_Id has been removed
2) I have explicity listed the columns to insert into, in the order of the SELECT statment
3) INSERT..SELECT does NOT line up columns for you by name -- it is by the order of the columns. So, you should ALWAYS explicitly list out the columns you are inserting into.

hope this helps.

also, see Jay's post. Much easier !!!!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources