Table with increment charter ID field
-
Hello everyone,
I am trying to create a table with ID field which increment automatically. ID must be 6 charters contain both letters and numbers (A00A00)
i.e. A00A00, A00A001 ….. A00A99, A00B01 and so on
I try to generate and update table with unique ID s by writing a trigger and updating ID field by calling a function. This work fine when I try to insert a single row, but I am having problem updating ID when trying to insert multiple rows at same time i.e. inserts into statement.
The function (which calculate next id), check same table, find last ID value and increment the id according to formula. The main problem with multiple rows is that there is no way to know which last ID was used. Is there any other way to create a column with increment charters ID, which following ( i.e. A00A00, A00A001 ….. A00A99, A00B01 and so on) format?
Note: ID must be 6 charters contain both letters and numbers (A00A00)
-
Not sure, but it sounds like you may need two things:
1) LastUpdate field with a datetime or timestamp designation to retrieve the last record.
B) A Commit Transaction wrapped around your inserts.
I wasn't born stupid - I had to study.
-
Create a function to convert integers to strings according to your format.
Create a table with IDENTITY column ID and computed column containing result of that function calculated from ID.
CREATE TABLE dbo.MyTable (
ID int IDENTITY (1,1) NOT NULL,
Code as dbo.CodeFunction (ID) ,
....
)
_____________
Code for TallyGenerator -
Sergiy's idea is exactly what we suggested for someone else to do:
below i'm pasting a function that will create a value AA001 thru ZZ999 (675999 values max before this function blows up
this might get you started: you can teak the idea for the combination you are looking for.
DROP TABLE X
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 676000)),
XCALCULATED AS CHAR((XID/26000)%26+65) --1st Letter
+CHAR((XID/1000)%26+65) --2nd Letter
+REPLACE(STR(XID%1000,3),' ','0'), --The 3 digit numeric part
SOMEOTHERCOL VARCHAR(30)
)
INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SELECT * FROM X
XID XCALCULATED SOMEOTHERCOL
1 AA001 WHATEVER
675999 ZZ999 MORESTUFF
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Ummm beat me to it... well, almost... please send beer, I already have enough pretzels...
--===== If the demo table exists, drop it
IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL
DROP TABLE #yourtable
--===== Create a demo table with just an IDENTITY column (your real table would have more columns)
SELECT TOP 6759999
IDENTITY(INT,0,1) AS SomeIDCol
INTO #yourtable
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a primary key and a calculated column that will automatically create the
-- correct alpha-numeric id in the form of a99a99 where A00A00 = 0 and Z99Z99 = 6,759,999
ALTER TABLE #yourtable
ADD CONSTRAINT PK_yourtable_SomeIDCol PRIMARY KEY CLUSTERED (SomeIDCol),
CharIDCol AS CHAR((SomeIDCol/260000)%26+65) --Left-most letter
+ REPLACE(STR((SomeIDCol/2600)%100,2),' ','0') --Left-most digit pair
+ CHAR((SomeIDCol/100)%26+65) --Right-most letter
+ REPLACE(STR(SomeIDCol%100,2),' ','0') --Right-Most digit pair
--===== Display the front and back of the table just to demo... it works.
SELECT * FROM #yourtable WHERE SomeIDCol <= 26100
SELECT * FROM #yourtable WHERE SomeIDCol >= 6700000
The key here is that whatever table you create, it must have an identity column. Point the calculated column to that identity column.
--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) -
P.S. It won't blow up after 6,759,999... but it will start the sequence over, so you may want to put a constraint on the IDENTITY column to be >=0 and < 6,760,000 just so it gives you some kind of indication that the number got too large.
Then, insert away... your alphanumeric ID will be automatically created no matter how many rows you insert (up to the max, of course).
--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) -
This question was posted on SQLTeam also, and I posted a similar answer there earlier today.
-
Dang!!! Spot on Michael... great minds think alike.
I'm trying to mello out in my old age... wasn't going to ask about why anyone would want to do something so insane... then I saw the other posts at the URL you posted... Now I think it's really insane... these things usually are the result of a "clever" business analyst and not a database developer.
--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)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply