How do I increment a DB id

Question

How do I increment a DB id

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

kipp Mr or Mrs. 500 Points: 576 More actions · Answer 1

Hello, can I ask you a quick question....

I have some data in a table that during a conversion we put in all lower and in all upper case. This is a name column in the table so it need to be Firstname M. Lastname, or just Firstname or Firstname Lastname.

Is there anyway to convert that in SQL Server?

Thanks in Advance..

Kipp

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

You should start a new topic for this question.

----------------------------------------------------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

kipp Mr or Mrs. 500 Points: 576 More actions · Answer 3

kipp

Mr or Mrs. 500

Points: 576

January 16, 2008 at 8:44 am

#768728

sorry about that.. i will do that.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

Nothing to be sorry about. You'll just get quick responses because it's going to be seen by a wider audience.

----------------------------------------------------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

Bob Fazio SSChampion Points: 10794 More actions · Answer 5

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%'

The easy way would be th change the table and make the ORG_KEY an identity field. Then you just leave it out of your insert, and go back to the output/scope_identity() stuff.

Otherwise this is also an option

select identity(INT,@MyId+1,1) as MyId,id,name,org_id

into #myTempProj

from Proj where project like '11450%'[/quote]

select * from #myTempProj

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Matt Miller (1/14/2008)
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?

A bit of confusion on my part... why, exactly, is that any better than Scope_Identity when being used for RBAR inserts?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

a few quick things:

- first one is - it doesn't have to be RBAR, since it's the same virtual table we've all come to know and love from triggers. Admittedly - my particular example doesn't doesn't bear that out, but still. You insert 400 - you get back 400.

- the reason you usually use scope_identity is to either pull data back from the record, or reuse it. This saves you the extra round-trip since you can bring back in one insgle shot what you wanted to get.

- it doesn't seem to have any of the issues that scope_identity has with potential for it to return "someone else's" IDs. As in - the scenario where all web users might be using the same ID to log in, SCOPE_IDENTITY() might return the last ID (inserted by another user). This one is very clean - it returns all of the rows (ids and all) that you just updated.

And the best part -you don't HAVE to manage the identity, get into the deadlocks, etc... that usually entails. You can go back to letting the DB manage the sequential ID's. It was a back-handed way to try to convince him NOT to do what he was about to do.

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Ok, thanks, Matt... good information. I'll get my arms around all this "new" 2k5 stuff, soon.

But I'm pretty sure that Scope_Identity isn't "login" sensitive as you suggest. That is, I'm pretty sure that no matter how many people might be logged in using the same name, Scope_Identity will always return correctly even in the presence of triggers and a heavy load.

Of course, I could be very wrong... Have you actually seen it where Scope_Identity returns the wrong identity in the presence of multiple identical logins or are you basing that on what someone else said? BOL seems to indicate that can't happen...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

I'm now getting the two lumped in... I'm talking @@IDENTITY, aren't I?

I gave up on both of those a while ago, because they felt "inadequate" (@@IDENTITY since it could grab from another session, and SCOPE_IDENTITY because it could grab from another table). I usually then revert to other more manual methods of getting said data (again - more like due to being burned by @@IDENTITY, which I have experienced first-hand).

----------------------------------------------------------------------------------
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?

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 10

indeed, you've mixed them up.

It's destiny for many of us dba's ...

Sometimes you have to proove you're human after all :w00t::D

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

If I remember correctly, @@IDENTITY has all the potential problems you describe including grabbing from the wrong table in the presence of triggers. I've never seen Scope_Identity fail in any of the manners you've described.

Of course, I could be wrong... like ALZDBA said, I'm just a human...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Now that I have them straight - the "issue" I had with it was a human error: creating something that works, and then having an extra statement put into the batch between the intended insert and the SCOPE_IDENTITY(), and then spending some time scratching head to figure out why it "failed" and started bring junk back.

Also - I just LOVE this one:

select SCOPE_IDENTITY() from MyTable

I found this in some code as an attempt from a junior guy to "steer" which identity he wanted.

At this point - I'm sure it's more visceral to me than is justified. It's just "once burned, twice shy"....

----------------------------------------------------------------------------------
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?