October 14, 2013 at 7:59 pm
Hi,
I have a table simple nothing to complicated
CREATE TABLE [dbo].[RHH] (
[UNIQUE_ID] varchar(50) NULL,
[First_Name] varchar(50) NULL,
[Last_Name] nvarchar(50) NULL,
nvarchar(50) NULL)
ON [PRIMARY]
with this table all the data gets inserted via an application except the UNIQUE_ID. This I am looking to increment as the data is inserted. Also it has to be in the format of say RHH100100 +1.
Im thinking an on insert trigger to update the column but not sure how to get the last value and increment that. ?
Hope this helps...
Ford Fairlane
Rock and Roll Detective
October 14, 2013 at 9:26 pm
I first thought on this is that you are about to create a serious bottleneck in your system - at best you will experience a little bit of blocking. At worst (and I have seen it this in action), you will end up with a system that has extremely poor performance and constant deadlocks.
Also, the trigger should have situations where multiple rows are inserted at the same time (e.g. INSERT INTO dbo.RHH SELECT * from anothertable) which will add complexity.
How important is the numeric portion of your unique_id ? Is it enough for it to be generation using a sql server IDENTITY column ? If it is then, you could easily do what you need using and IDENTITY column and, if you need to show the value with the "RHH" prefix, you could also have a computed column.
The following would achieve this
CREATE TABLE [dbo].[RHH] (
[UNIQUE_ID] AS 'RHH' + RIGHT ('00000000' + CONVERT (VARCHAR, MyIdentity), 10),
[First_Name] varchar(50) NULL,
[Last_Name] nvarchar(50) NULL,
nvarchar(50) NULL,
MyIdentity INT IDENTITY (1, 1)
)
ON [PRIMARY]
October 14, 2013 at 9:38 pm
Certainly makes sense I will try it out and let you know.
Appreciate the idea.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
October 15, 2013 at 12:32 am
happycat59 (10/14/2013)
I first thought on this is that you are about to create a serious bottleneck in your system - at best you will experience a little bit of blocking. At worst (and I have seen it this in action), you will end up with a system that has extremely poor performance and constant deadlocks.Also, the trigger should have situations where multiple rows are inserted at the same time (e.g. INSERT INTO dbo.RHH SELECT * from anothertable) which will add complexity.
How important is the numeric portion of your unique_id ? Is it enough for it to be generation using a sql server IDENTITY column ? If it is then, you could easily do what you need using and IDENTITY column and, if you need to show the value with the "RHH" prefix, you could also have a computed column.
The following would achieve this
CREATE TABLE [dbo].[RHH] (
[UNIQUE_ID] AS 'RHH' + RIGHT ('00000000' + CONVERT (VARCHAR, MyIdentity), 10),
[First_Name] varchar(50) NULL,
[Last_Name] nvarchar(50) NULL,
nvarchar(50) NULL,
MyIdentity INT IDENTITY (1, 1)
)
ON [PRIMARY]
happycat - with a tiny modification to suit my needs this worked a real charm thanks for you assistance !!!!
Hope this helps...
Ford Fairlane
Rock and Roll Detective
October 15, 2013 at 5:19 am
No probs, glad to help out (and to have you avoid a pretty serious problem)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy