January 16, 2008 at 8:29 am
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
January 16, 2008 at 8:36 am
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
January 16, 2008 at 8:44 am
sorry about that.. i will do that.
January 16, 2008 at 8:47 am
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
January 17, 2008 at 2:40 pm
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
January 17, 2008 at 9:17 pm
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
January 17, 2008 at 10:18 pm
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?
January 17, 2008 at 10:49 pm
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
January 18, 2008 at 7:52 am
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?
January 18, 2008 at 8:07 am
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
January 18, 2008 at 8:20 am
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
January 18, 2008 at 8:45 am
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?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply