January 14, 2008 at 5:32 am
Help! I was wondering how I increment a database id as I insert values into a table in SQL Server? I need to grab the last inserted value and then increment that by 1. I am not sure if it has the IDENTITY property put on it in the table and if it does not I do not have access to add it. Can any one help?
Thanks
January 14, 2008 at 6:20 am
Assuming I understood the question, if you're asking how to determine what the last identity value inserted was is to use the SCOPE_IDENTITY() function. This returns the last Identity value generated by the script, procedure or trigger it's called from.
If you're asking what to do when you don't have an identity field, you can use the MAX statement to get the highest value and then add one to it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2008 at 6:25 am
Thanks for the quick reply. I am pretty new at the SQL Server stuff... if I am insert values in what would be syntax be on those two statements (IDENTITY and MAX?)? Thanks so much...
January 14, 2008 at 6:39 am
You'd use the SCOPE_IDENTITY like this:
INSERT INTO table
(Desc,UpdateDate,Whatever)
VALUES
('My Description','1/14/2007','Something)
SELECT SCOPE_IDENTITY()
That would insert a row into the table and then give you the identity value generated by that table from the insert. If you didn't have an identity value, but you had an integer column that you generated a value to add to it, quick & dirty, here's how you could do that
DECLARE @MyId int
SELECT @MyId = MAX(Id)
FROM table
INSERT INTO table
(Id,Desc)
VALUES
(@MyId+1,'My Description')
I say that's quick and dirty because, when using that kind of approach, you need to worry about locking and concurrency. So there are more sophisticated ways of doing it than what I showed.
Also, you might want to know about something called IDENTITY_INSERT. It's a way of adding your own value to an identity column. There are tons of caveats around it's use, so I'd suggest reading up on it in the Books Online (BOL) before you use it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2008 at 6:41 am
I will read up on that, and thanks so much... I just was not for sure what to even search or look for...
Thanks Again for your help.
January 14, 2008 at 7:22 am
on the other hand,
if you use identity in your column definition, sqlserver will generate the next number for you.
And with sql2005 you could just add
OUTPUT [yourIDcolumn]
to your statement and you'd have the value returned with the returnvalue of the insert statement and you can skip the scope_identity select.
CREATE TABLE #tbl_whatever (colid int identity (1,1), username varchar(50) DEFAULT suser_sname(),
uniqueid uniqueidentifier DEFAULT NEWID(),
Creationdate datetime default getdate(),
rANDOMVALUE int default rand()*5000000)
insert into #tbl_whatever (username)
OUTPUT inserted.* values (DEFAULT)
declare @outputtable table (newvalue int, oldvalue int)
update #tbl_whatever
set randomvalue = randomvalue + 1
OUTPUT inserted.randomvalue, deleted.randomvalue into @outputtable
select * from @outputtable
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2008 at 7:35 am
Since you're in 2005 - you can avoid all of the scope_identity nonsense by using the OUTPUT predicate from within the INSERT statement.
It looks something like
declare @nextid as integer
declare @tbl table (id int)
INSERT into dbo.mytable (lname)
OUTPUT inserted.id into @tbl
values('bob')
select @nextid=id from @tbl
select @nextid --now do something with it
The bigger question is - why do you need it? All of these approaches are making assumptions that you're dealing with only one record at a time, which might be a messy assumption in a lot of cases. Also - forcing SQL Server to only operate one record at a time tends to make it behave badly (it likes to play with LOTS of toys at the same time).
What are you doing with it?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 7:43 am
I knew of the OUTPUT clause, but I'd only really thought of it for stuff like auditing. This is really useful. So, if you had a multi-row insert, you'd just output to a table variable? I'm going to have to experiment a bit with this.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2008 at 7:56 am
hey thanks... I am going to have to go with the Max function below, one question I would have is how can I put this in a loop to increment or keep calling the MAX function and insert the lastest value in?
My SELECT Query pulls back several records I need to change.
DECLARE @MyId int
SELECT @MyId = MAX(ORG_KEY) from orgs
select @MyId+1,id,name,org_id from Proj where project like '11450%'
January 14, 2008 at 8:07 am
Grant Fritchey (1/14/2008)
I knew of the OUTPUT clause, but I'd only really thought of it for stuff like auditing. This is really useful. So, if you had a multi-row insert, you'd just output to a table variable? I'm going to have to experiment a bit with this.
Indeed, everything is available in the inserted-object and can be externalized using the output clause.
CREATE TABLE #tbl_whatever (colid int identity (1,1), username varchar(50) DEFAULT suser_sname(),
uniqueid uniqueidentifier DEFAULT NEWID(),
Creationdate datetime default getdate(),
rANDOMVALUE int default rand()*5000000)
insert into #tbl_whatever (username)
OUTPUT inserted.*
values (DEFAULT)
insert into #tbl_whatever (username)
OUTPUT inserted.*
select username + '_Run2' from #tbl_whatever
insert into #tbl_whatever (username)
OUTPUT inserted.*
select username + '_Run3' from #tbl_whatever
insert into #tbl_whatever (username)
OUTPUT inserted.*
select username + '_Run4' from #tbl_whatever
-- what auditors like about OUTPUT
declare @outputtable table (newvalue int, oldvalue int)
update #tbl_whatever
set randomvalue = randomvalue + 1
OUTPUT inserted.randomvalue, deleted.randomvalue into @outputtable
select * from @outputtable
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2008 at 8:12 am
Here we have a double edged sword. On the one side, I've got a more effecient tool to use in my queries. On the other side, you just handed me a bunch of work because I know I've got some queries I can rewrite to benefit from this.
So 😛
Thanks.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2008 at 8:17 am
as long as you don't send me the bills, I'm happy with that. :hehe:
Indeed SQL2005 has some nice new stuff...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2008 at 8:19 am
Grant Fritchey (1/14/2008)
I knew of the OUTPUT clause, but I'd only really thought of it for stuff like auditing. This is really useful. So, if you had a multi-row insert, you'd just output to a table variable? I'm going to have to experiment a bit with this.
Or - just like you might do with a trigger - you can put it into an existing table. So you could do something like
Delete from MyTable
Output deleted.*,getdate() into MyHistoryTable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 8:21 am
Yes, that's exactly the type of auditing function that I had understood the OUTPUT function to be useful for. I just hadn't made the leap to using it with more mundane functions. I'm busy figuring out the rewrites I need on one of my projects right now.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2008 at 9:00 am
kipp (1/14/2008)
hey thanks... I am going to have to go with the Max function below, one question I would have is how can I put this in a loop to increment or keep calling the MAX function and insert the lastest value in?My SELECT Query pulls back several records I need to change.
DECLARE @MyId int
SELECT @MyId = MAX(ORG_KEY) from orgs
select @MyId+1,id,name,org_id from Proj where project like '11450%'
that technique can cause duplicates and performance problems if you create lots of orgs. a unique index on orgs.ORG_KEY would help and ensure no duplicates are created for orgs.ORG_KEY, but won't protect you from collisions (two sessions trying to add the same ORG_KEY).
here's a table and stored procedure for sequencing:
create table Sequencing (
objectName varchar(128) not null,
sequence int null default -1,
constraint PK_SEQUENCING primary key( objectName )
)
create procedure dbo.getNextSequence_sp
@object varchar(256),
@reserve int = 1,
@sequence int OUTPUT
as
begin
begin transaction
update Sequencing
set sequence = sequence + @reserve
where objectName = lower(@object)
if (@@rowcount < 1)
insert into Sequencing (objectName, sequence ) values (lower(@object), @reserve)
set @sequence = (select sequence - @reserve
from Sequencing where objectName = lower(@object))
commit transaction
end
...
DECLARE @MyId int
--SELECT @MyId = MAX(ORG_KEY) from orgs
exec getNextSequence_sp "orgs.ORG_KEY", 1, @MyId out
with this technique, you're 'reserving' the sequence value and need not use it immediately (which is often the case with an interactive application). if you know you're going to be added 20 things, all 20 values can be reserved at once.
the downside of course is the table/proc must be used when creating new values for the reserved object.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply