Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All
I have to generate serial number which will insert in table if we delete any row from the table then it should serialize the number now I have a Store procedure through which I am inserting data in the table from Front End so the same I have to insert serial number in the table. How it is possible Please help

Thanks To All
Indrajit Dasgupta
Posted
Comments
PrashantSonewane 17-Apr-13 6:33am    
"if we delete any row from the table then it should serialize the number " means? some details please. Example would be good..
IndrajitDasgupat 17-Apr-13 6:58am    
suppose I have 3 Rows
1 x A
2 Y B
3 Z C
Now I am deleting row No 2 then it should display like below
1 x A
2 Z C
CHill60 18-Apr-13 9:49am    
This is more like ROW_NUMBER than a Serial number so my solution isn't quite right - I've updated it along the lines of RedDKs solution 2 but using a stored procedure

You need to set up a column with the IDENTITY property (link to documentation[^]

The number will automatically be incremented everytime you add a row. If you delete a row the id number is not reused, so the number can be deemed to always be unique within this table.

Be very cautious of attempting to do something similar yourself manually (e.g. using MAX(id) to assign the "next" number) as any solution may not be robust in a multi-user environment

[EDIT - OP has made it clear that the id numbers must be consecutive after a deletion]
With a nod to solution 2 as this is very similar ...
First I will create a sample table put some data in it and query it to show how the ID has been generated for us
SQL
-- Create sample table with arbitrary fields
CREATE TABLE dbo.EXAMPLE
(
    [ID] [int] IDENTITY(1,1),   -- this is the important one
    [FLD1] [varchar](20),
    [FLD2] [varchar](20)
)

-- in later versions of SQLServer than mine you can do this in a single statement
insert into EXAMPLE VALUES('x','A') -- note don't supply a value for the serial number
insert into EXAMPLE VALUES('Y', 'B')
insert into EXAMPLE VALUES('z', 'C')

SELECT * FROM EXAMPLE
produces the output
ID	FLD1	FLD2
1	x	A
2	Y	B
3	z	C
Now that we have the data set up I created a stored procedure to reserialise this table (note I haven't tried to make this generic but it is possible, if long-winded, to do so)
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ReserialiseEXAMPLE]
AS
BEGIN

    SET NOCOUNT ON;
    SELECT FLD1, FLD2 INTO #RESERIAL FROM EXAMPLE -- Note do not include ID
    DROP TABLE EXAMPLE

    SELECT IDENTITY(int, 1,1) as ID, *  INTO EXAMPLE FROM #RESERIAL ORDER BY ID
    DROP TABLE #RESERIAL
END
GO
Now delete a record and add a new one and see what's in there...
SQL
DELETE FROM EXAMPLE WHERE FLD2 = 'B'
insert into EXAMPLE VALUES('a', 'D')
SELECT * FROM EXAMPLE
and we get
ID	FLD1	FLD2
1	x	A
4	a	D
3	z	C
But now call the stored procedure and compare the results
SQL
dbo.CHill60Reserialise
SELECT * FROM EXAMPLE
gives us
ID	FLD1	FLD2
1	x	A
2	a	D
3	z	C

Couple of things to point out ... this is an incredibly inefficient way of dealing with this so I would advise finding a way of clumping together all of your deletions (e.g. mark the records to be purged) and run the SP as little as possible.
Also (as per comments in Solution 2) this was affected by doing an insert before the reserialise and can also be affected by order by.

Lastly - you specifically stated SQL2000 in your post ... be aware that from SQL2005 onwards there are better ways of generating ROW_NUMBER or RANK - which is essentially what you're trying to do here
 
Share this answer
 
v2
Comments
Maciej Los 17-Apr-13 15:30pm    
+5
I think you're after a group of related TSQL statements, all of which summarily perform the above desired work:
USE [cpqaAnswers]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tbl_ID_ProcessDCI]') AND type in (N'U'))
DROP TABLE [cpqa].[tbl_ID_ProcessDCI]
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCI](
	[Idx][int],
		[Data_01][nvarchar](17),
			[Data_02][nvarchar](18)
			)

USE [cpqaAnswers]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tbl_ID_ProcessDCIIdx]') AND type in (N'U'))
DROP TABLE [cpqa].[tbl_ID_ProcessDCIIdx]
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCIIdx](
	[Idx][int]IDENTITY(1,1),
		[Data_01][nvarchar](17),
			[Data_02][nvarchar](18)
			)
INSERT INTO [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCI]
	VALUES(1,  'x',  'A'),(2,  'Y',   'B'),(3,  'Z',   'C')
	
SELECT * FROM [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCI]	
/*
	Idx	Data_01	Data_02
	~~~~~~~~~~~~~~~~~~~~~~~
	1	x	A
	2	Y	B
	3	Z	C
*/	
DELETE FROM [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCI] WHERE [Idx] = 2	
SELECT * FROM [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCI]	
/*
	Idx	Data_01	Data_02
        ~~~~~~~~~~~~~~~~~~~~~~
	1	x	A
	3	Z	C
*/
INSERT INTO [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCIIdx]
	SELECT [Data_01], [Data_02] FROM [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCI]
	
SELECT [Idx]
      ,[Data_01]
      ,[Data_02]
  FROM [cpqaAnswers].[cpqa].[tbl_ID_ProcessDCIIdx]
/*
	Idx	Data_01	Data_02
        ~~~~~~~~~~~~~~~~~~~~~~
	1	x	A
	2	Z	C
*/

Of note, primarily, is that DROP of the orginal table. And if you were to do this over and over repeatedly, the DROP of the indexed table is also crucial. See the intermediate step? And don't be fooled by the two different indexes, right? That's why the auto-index has to completely retabulate; and as Solution 01 author suggests, the programmer really is at the mercy of the IDENTITY mechanism. And finally, consider the operative ORDER BY ... this is another potential wrench in a scalar of this mockup.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900