Auto-Number without using Identity Column in MS-SQL Server
Hi,
I have a table without any Identity field. The table contains a coulmn as 'ReqID'. The format of the data in the column 'ReqID' should be 'MMDDYYYYNNNNN', where
MMDDYYYY = Current Date
NNNNN=Unique number in sequesnce, within range 10000-99999 (The number should be in sequence[NOT RANDOM] for each new row inserted into table)
i.e. the value will be created by MMDDYYYY+NNNNN.
I am able to have MMDDYYYY from getDate() cammand. Now, Problem is to generate the auto-number 'NNNNN'. I can not introduce any NEW Identity column (or a new table). Also the records may be inserted using more than one database connections.
Please help me, how to do it?
Ya, one thing that... I can change the existing column 'ReqID' as identity column, but then how to incoperate date part??
Thanks in advance.
Gyanendra Dwivedi
gm_dwivedi@sify.com
[957 byte] By [
gm_dwivedi] at [2007-11-11 8:46:57]
# 1 Re: Auto-Number without using Identity Column in MS-SQL Server
CREATE TABLE TheTable (ReqID NVARCHAR(13) PRIMARY KEY CLUSTERED, Data NVARCHAR(100))
DECLARE @date NVARCHAR(8)
SET @date = '05282006'
INSERT INTO TheTable (ReqID, Data)
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0528200610000'
INSERT INTO TheTable (ReqID, Data)
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0528200610001'
SET @date = '05292006'
INSERT INTO TheTable (ReqID, Data)
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0529200610000'
INSERT INTO TheTable (ReqID, Data)
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0529200610001'
select * from TheTable
DROP TABLE TheTable
It would, of course, be a good idea to wrap that icky code in a user-defined function...
Or you could have a trigger generate the key for you.
Rune
