Issue with IDENTITY - 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

Transact-SQL (2000)

Issue with IDENTITY

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 15:23:32

Here Iam getting a error message and I want to insert the records starting from the max+1

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@max_count'.

DECLARE @offer_id int
SET @offer_id =56579
DECLARE @max_count int
SET @max_count = (SELECT max(offer_id)FROM tbl_offers)
SET @max_count = @max_count +1

SELECT IDENTITY(int,@max_count,1) as offer_id,
BuyersName,OfferPrice,OfferDate,

INTO #temp_info
FROM tbl_offers_trans
WHERE offer_id =@offer_id

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 16:25:51

When I use a number say 5 the identity works from 5 and when I use @max_count it gives error

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 16:26:20

please suggest me a solution

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 16:29:48

That will require dynamic sql. Why does it matter what seed to start with? Why not just add @max_count in your select statement:

DECLARE @offer_id int
SET @offer_id =56579
DECLARE @max_count int
SET @max_count = (SELECT max(offer_id)FROM tbl_offers)
SET @max_count = @max_count +1

SELECT IDENTITY(int,@max_count,1) as offer_id,
BuyersName,OfferPrice,OfferDate,

INTO #temp_info
FROM tbl_offers_trans
WHERE offer_id =@offer_id

SELECT offer_id + @max_Count AS offer_id
FROM #temp_info

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 16:41:48

I have to insert these records into a table and get the offer_id(incremental_id) from there...
prop_id Name
2002093525 Boyd
2002093525
2002105239 JOSEPH ARMENDAREZ
2002105239
2002105239
2002105239
2002105252 BOYD
2002105252
2002117330 Juan Atilano
2002117330 Juan Atilano
2002117330 Juan Atilano
2002117330 Juan Atilano
Now what I have done is got the max of offer_id from tbl_offers and then inserted into temp_Table
with max + 1 id as the start so that i can use these offer_id to do some inner join with other tables because there is no other unique id to follow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 17:36:38

I tried something like this but still giving me issues

DECLARE @offer_id int
SET @offer_id =56579
DECLARE @max_count int
SET @max_count = (SELECT max(offer_id)FROM tbl_offers)
SET @max_count = @max_count +1

DECLARE @SQL varchar(7000)
SET @SQL = 'SELECT IDENTITY(int,'+ CONVERT (varchar(200),@max_count)+',1) as offer_id,
BuyersName,OfferPrice,OfferDate

INTO #temp_info
FROM tbl_offers_trans
WHERE offer_id =@offer_id'

EXECUTE (@SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 17:38:11

Why can't you just use 1 as the identity seed, then pretend the seed is incremented already via:

SELECT offer_id + @max_Count AS offer_id
FROM #temp_info

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 17:51:09

Hi Tara,
NO Ican't because I want the exact Id which I will be inserting into the next table...

before inserting iam taking a count of the records that I will be inserting so that I will know the ids and according to that I need to pull data from different table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 17:54:37

But you can do that with my solution already.

INSERT INTO NextTable(offer_id, SomeColumnA, SomeColumnB)
SELECT offer_id + @max_Count AS offer_id, SomeColumnA, SomeColumnB
FROM #temp_info

You could also use the IDENTITY_INSERT option to fix the data once inserted into your temporary table using seed of 1.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-14 : 18:06:09

Hi Tara,
I think iam not explaining it properly...Sorry...
offer_id is identity column in the NEXT table and iam getting the max of NEXT table.Now I have a select statement which gives me a count say 5. and I have to insert that into into NEXT table and the temp table and the temp table should contain the same identity value of the NEXT table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 18:10:49

You can still do that with my solution. The temp table would start with 1, then you'd get the data out by incrementing it, thus faking this solution.

So, let's say #Temp already has 5-100 as the identity values, then:

SELECT IdentColumn
FROM #Temp

That'll show you 5-100.

Now here's faking it:

#Temp now contains 1-95. Then:

SELECT IdentColumn + 5
FROM #Temp

The result set will display 5-100 just like the first solution did. And 5 can be a variable.

IDENTITY_INSERT will also fix your problem although it requires escalated privileges so it shouldn't be used except for DBA type stuff.

Tara

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-15 : 02:21:42

all variables must be outside the quotes...

quote:
Originally posted by sqllearner

I tried something like this but still giving me issues

DECLARE @offer_id int
SET @offer_id =56579
DECLARE @max_count int
SET @max_count = (SELECT max(offer_id)FROM tbl_offers)
SET @max_count = @max_count +1

DECLARE @SQL varchar(7000)
SET @SQL = 'SELECT IDENTITY(int,'+ CONVERT (varchar(200),@max_count)+ ',1) as offer_id,
BuyersName,OfferPrice,OfferDate
INTO #temp_info
FROM tbl_offers_trans
WHERE offer_id =' + @offer_id

EXECUTE (@SQL)

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources