Auto-Number without using Identity Column in MS-SQL Server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Auto-Number without using Identity Column in MS-SQL Server

  1. #1
    Join Date
    Jul 2005
    Posts
    9

    Question 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

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Code:
    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
    If you hit a brick wall, you didn't jump high enough!

  3. #3
    Join Date
    Jul 2005
    Posts
    9
    @Rune
    Thanks for reply.
    I have got another similar solution for the problem.
    Please visit
    http://www.codeguru.com/forum/showth...75#post1411375
    for details.

    -Gyanendra Dwivedi

Similar Threads

  1. Replies: 0
    Last Post: 04-29-2002, 07:43 AM
  2. Can Sql Server do this?
    By DavidR in forum Database
    Replies: 5
    Last Post: 02-14-2001, 11:46 PM
  3. using identity for sql server
    By guy in forum VB Classic
    Replies: 1
    Last Post: 02-14-2001, 03:48 AM
  4. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 03:27 PM
  5. How do I use SQL Server Column Type 'IsRowGuidCol'
    By Larry Rebich in forum VB Classic
    Replies: 0
    Last Post: 04-23-2000, 09:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center