inserting a record with scope in the identity

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)

inserting a record with scope in the identity

Author

Topic

stikiflem1
Starting Member

6 Posts

Posted - 2005-02-07 : 00:48:28

i have this problem which i hope somebody could help out:

- table's primary key (IDNum) is identity which range from 0 to 5,000,000
- i have two users putting new records all the time.
- user1 should have a range of 0 - 2,500,000
- user2 should have a range of 2,500,001 - 5,000,000

say user1 inputed a record and got the IDNum = 1
user2 inputed a record from his range and got the IDNum = 2,500,001

after the second insert of user1, he will get IDNum = 2,500,002

Prob:
How can i have the MS SQL to insert records with their respective ranges?
like User1 should follow the range: 0 - 2,500,000
and user2 following the range: 2,500,001 - 5,000,000

note that i should not use the "SET IDENTITY_INSERT to ON" or inserting
explicitly the IDNum

pls. help because im gonna get fired if nobody will.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-07 : 01:17:10

What you're suggesting can't be done with an identity.
The real answer is that it shouldn't matter what value is hels in the database - if you hold the user also then you can add 2500000 to the value whenever it is returned when it was inserted by user2.

If whoever is designing this mess thinks they need the value in the table then create another column which holds the value incremented for user2 but not for user1 (could be a calculated column).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stikiflem1
Starting Member

6 Posts

Posted - 2005-02-07 : 11:52:24

thanks for the reply. i appreciate it very much. it didnt really answered my prob
but it got me thinking in the right direction. :)
i think i already got the answer already.
i think i can solve this little problem by just deleting
the identity and just use something to increment my table
every time i do INSERTS.

say i have table named "MyTable"
IDNum = the primary key
Name = other field

i can play with this instead:

insert into MyTable
select (select max([id])+ 1 from MyTable),'hello'

that way, i can just set the range to the other users.

my only worry is this:
i have heard that doing MAX is a little slow. will this be still a reliable
SQL statement to think that i have 20 users doing transactions at the same
time in the MyTable?

if this is a little too amateur SQL, what other efficient alternatives can i have?

pls. help.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-07 : 12:39:54

Create a table with just the current id.
create table IDs(username varchar(20) not null primary key , id int)

then
declare @id int
begin tran
if not exists (select * from IDs where username = system_user())
begin
insert IDs select system_user(), 1 -- put logic here for the user ranges or prepopulate the table with a start id for each user.
select @id = 1
end
else
begin
update IDs set @id = id + 1, id = id + 1 where username = system_user()
end
insert mytbl select @id, 'hello'
commit tran

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stikiflem1
Starting Member

6 Posts

Posted - 2005-02-08 : 00:47:58

thanks for the reply. i appreciate it very much. it didnt really answered my prob
but it got me thinking in the right direction. :)
i think i already got the answer already, i think.
i think i can solve this little problem by just deleting
the identity and just use something to increment my table
every time i do INSERTS.

say i have table named "MyTable"
IDNum = the primary key
Name = other field

i can play with this instead:

insert into MyTable
select (select max([id])+ 1 from MyTable),'hello'

my only worry is this:
i have heard that doing MAX is a little slow. will this be still a reliable
SQL statement to think that i have 20 users doing transactions at the same
time in the MyTable?

if this is a little too amateur SQL, what other efficient alternatives can i have?

pls. help.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-08 : 07:28:44

Have another look at my previous post.
It does the same thing but uses another table to hold the users current id seed so doesn't have to use max on the table.

If you have an index on user, id then the max should be quick but might cause deadlocks.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stikiflem1
Starting Member

6 Posts

Posted - 2005-02-09 : 01:58:55

thanks a lot, it helped me out a lot. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources