May 7, 2008 at 4:58 am
I have to create a unique(combined) filed based on the Id when I Insert Data eg.
if next Id = 2 the other Field will be as 05-MSN-002 . So the Number Im looking to Generate is (002). I want to generate this before I insert the data in the table so how I can retrieve the Next Item Id from a table?
thanks in Advance
May 7, 2008 at 5:34 am
You could simply select the max value of id column and add 1
Select MAX(your_id_col) + 1 from your_table
This will work fine and if you are the only one doing the insert then there is no risk of errors occuring.
However, if other people will be carrying out this insert as well there is a risk that an insert will occur between you getting the next id number and actually doing the insert resulting in your number being wrong.
Another apporach would be to use a stored proc and do you insert in two parts. Part one inserts a blank line (or a line with temporary values) you could then get @@identity from the inserted row and using this cary out an update on the same row with all the correct values. Stick all this in a BEGIN TRAN, END TRAN block to ensure that it all rolls back if it goes wrong.
Tim
May 7, 2008 at 5:50 am
I would say that taking the identity is the better one. Finding out max() will cause a scan on the column which will slow down the insertion process.
May 7, 2008 at 6:28 am
I just came up with a better way
You can use @@identity inside the first insert and therefore should be able to build your reference code on the fly.
I did this...
Created a table
CREATE TABLE insert_check(
insert_check_ref [int] IDENTITY(1,1) NOT NULL,
[some_value] varchar(50) NULL)
Created a proc
ALTER procedure [dbo].[p_insert_check]
(
@some_value varchar(50)
)
as
begin
insert insert_check (some_value)
values(@some_value + CAST(@@identity As Varchar(50)))
select * from insert_check
end
...this returns the parameter passed in concatinated with @@identity from the row inserted
Tim
May 7, 2008 at 7:01 am
There would be a problem with my first Item. It will not return 1 it will return null......
May 7, 2008 at 7:07 am
looks like that was a garden path or a red herring or something
I failed to notice that at that point @@identity is actually returning the previous identity value and not the new one.
May 8, 2008 at 10:00 am
create table #t (
i int identity(1,1),
a varchar(10),
b varchar(10)
)
go
alter proc t_insert(@b varchar(10))
as
begin
begin tran;
insert into #t (b) values(@b);
update #t set a='KEY-'+cast(ident_current('#t') as varchar(10))
where i=ident_current('#t');
select * from #t where i=ident_current('#t');
commit work;
end;
go
exec t_insert 'some data';
select * from #t
I would strongly recommend reading up on the differences between @@identity, scope_identity and ident_current() so you can choose which function will work best for your situation.
Steve G.
May 8, 2008 at 11:35 am
thank you all I found the right Solution. Here is if someone is interested:
SELECT case when Max(Id)+1 is NULL then IDENT_CURRENT ('tblJobs') else Max(Id)+1 end from tblJobs
this one solves me the Problem for getting 1 before I insert the first time. the rest of the String I handle in my c# Code.
May 8, 2008 at 12:52 pm
If you do things this way, you will most definitely have problems. If you have more than one connection, you can have both connections get the same value for the identity and then step on each other when they try to do the insert/update. Wrapping the update in database transactions will not prevent this problem.
Consider the following sequence:
conn1: select max(id)
conn2: select max(id) <-- gets the same number as conn1
conn1: do updates through C# code
conn2: do updates through C# code <-- writes over conn1's updates
And, yes, this can and will happen if you have more than user. And, speaking from experience, these types of problems can be murder to try and diagnose because they never happen while you're testing (because there's only one of you) and rarely happen in production. You just get comments about 'bad data' or 'the system is losing my data', and no way to verify what's going on.
Also, if you use the stored proc (or even just the same sequence of commands) I posted earlier you will never run into the problem of getting a NULL for the first entry, and you'll have a lot fewer problems with concurrency.
May 8, 2008 at 2:28 pm
Ident_Current() will get you the most recent identity value for a table. That would be better than select the max(ID).
On the other hand, is it possible to either use a calculated column for this, or an "instead of insert" trigger? Either one will be better, in terms of not generating duplicate values for concurrent connections.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply