Max ID Column - 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)

Max ID Column

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2007-07-26 : 20:22:45

Hi Guys,

I am trying to increment the RECID in this query. Can you guys help me with this. I am importing the data into this table once a day.

INSERT INTO TaInfo(media,length,recid)
SELECT media,length,(select max(recid) from Tainfo) FROM TEMP_PROGRAMS

as the RECID is a primary key in the Tainfo table it is not allowing duplicates. For some reason i cannot use Identity Column. I tried the Bulk Update of the rows incrementing by one but because of the PrimaryKey on RECID it is not allowing.
what i want is while inserting the data into TaInfo i want the recid incrementing by 1. Any Idea.?
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-26 : 20:32:35

max(recid) + 1?

And why can't you just use identity?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

vk18
Posting Yak Master

146 Posts

Posted - 2007-07-26 : 22:31:40

quote:
Originally posted by tkizer

max(recid) + 1?

And why can't you just use identity?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

This won't work either. because with max(recid) + 1 it will increment all the columns with the same id.
Any way i got another idea to solve this thing
Thx

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-27 : 00:51:42

IF you want to do this type of operation you have to set the recid as identity and doing the bulk insert will not have any issue...
ok now for this...

create a function which returns the maxid + 1 and use this to insert statement as below

--function returns maxid + 1
create function f_ts()
returns int
as
begin
declare @t int
select @t = max(id) + 1 from ttt
Return @t
End
--destination table
create table ttt(col1 int, col2 varchar(20), id int)
insert ttt
Select 1, 'raju', 1 union all
Select 1, 'viju', 2

--source table table
create table rr(col1 int, col2 varchar(20))
insert rr
Select 5, 'visu' union all
Select 6, 'ram'

--perform insert statement
insert ttt
Select col1, col2, dbo.f_ts()
from rr

--------------------------------------------------
S.Ahamed

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 11:26:20

What Tara meant was why cant you use Identity column for the RecId and let SQL Server generate the Id's for you?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vk18
Posting Yak Master

146 Posts

Posted - 2007-07-27 : 13:01:25

quote:
Originally posted by dinakar

What Tara meant was why cant you use Identity column for the RecId and let SQL Server generate the Id's for you?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Man i told you for some reason i should not use Identity column. I know about this. I got another idean and it is working now

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-27 : 13:05:48

We know what you said, but we are asking why.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources