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

[gm_dwivedi]

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

[DanielaTm]

Hi,
I had a situation like this once. I don't know if it is a simplier way to do it, or more professional, but look how I did it:

1. I created a table to store a single record: the current seq (NNNN you called it) let's say TblCrtNumber
2. In code, when insert intoi my table, I builded it from code : currentDate+the value from TblCrtNumber. And incremented for the next time NNNN

In TblCrtNumber you can control the record if you want to, decremented it when deleted if you want

Hope it helped

[vplag]

you can create a stored procedure that will load / return the maximum NNNNN number.

then in the insert statement, call this stored procedure, add 1.

Hope that it will helps

Regards

VPlag

[gm_dwivedi]

@ DanialaTm and Vplag
Thanks for the reply.
Based on your suggestion, I have created a sample code (Successfully Running) for reference by other Forum Members.

*************************************************************
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


-- Create table script
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO

CREATE TABLE [dbo].[Table2] (
[text_col] [varchar] (200) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table2] ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[text_col]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



-- program to generate and insert record into table
-- I assume that latest record is not deleted.

Declare @txt_RequestCount varchar (20)
Declare @txt_Requestid varchar (20)
Declare @txt_temp varchar (20)

-- If (1)no record found in the table or (2) Recorcount has exceeded 999 then reset it.
if not exists(select * from table2)
select @txt_RequestCount='1'
else
begin
select @txt_temp=max(text_col) from table2
if convert(int,Right(@txt_temp,3))>999
select @txt_RequestCount='1'
else
select @txt_RequestCount=convert(varchar(20),(convert(int,Right(@txt_temp,3))+1))
end

--For your case yyyyXXX i.e like 2006001,2006002 etc.
Select @txt_Requestid = convert(Varchar(20),datepart(yyyy,getdate())) + Right('000'+ @txt_RequestCount,3)

print @txt_Requestid
print convert(Varchar(20),datepart(yyyy,getdate()))
print Right('000'+ @txt_RequestCount,3)
print convert(Varchar(20),datepart(yyyy,getdate())) + Right('000'+ @txt_RequestCount,3)

insert into table2 (text_col) values(@txt_RequestId)

-- After it reaches to 2006999, the table has to be emptied to prevent primary key voilation.
*********************************************************

[elizas]

Lets have an Employee table in which some employee records are saved with the unique identity numbers for each employee. Now, suppose some records were deleted from the Employee table due to some reasons and now I want all the deleted records from Employee table,which may not be possible now as records are deleted from the table,but we can get the list of Unique Identity records deleted from Employee table by using query.

To demonstrate this first of all we have to create an Employee Table with some records inserted to it.Then we can delete some selected records from the Employee table to verify the output.


Hope u find this tip very useful.

http://www.mindfiresolutions.com/How-to-get-the-Deleted-Identity-Numbers-in-SQL-Server-2005-799.php

[dglienna]

Please don't revive DEAD threads. The last reply was 4 YEARS AGO!