在sql 2000中生成Seial Number [英] Generate Seial Number in sql 2000
问题描述
大家好
我必须生成序列号，如果我们从表中删除任何行，它将插入表中然后它应该序列化数字现在我有一个Store程序，我通过它插入数据在前端表中，所以我必须在表中插入序列号。怎么可能请帮助
感谢所有
Indrajit Dasgupta
< blockquote>您需要设置一个具有IDENTITY属性的列（链接到文档 [ ^ ]
每次添加行时，数字会自动递增。如果删除一行，则不会重复使用ID号，因此数字可以是在这个表中被认为总是独一无二的。
要谨慎尝试自己手动做类似事情（例如使用MAX（id）分配下一个号码）因为任何解决方案在多用户环境中可能不健壮
对解决方案2点头，因为这是非常的类似......
首先，我将创建一个示例表，在其中放入一些数据并查询它以显示如何为我们生成ID
- 创建包含任意字段的样本表
创建 TABLE dbo.EXAMPLE
（
[ID] [ int ] IDENTITY （ 1 ， 1 ）， - 这是重要的一个
[FLD1] [ varchar ]（ 20 ），
[FLD2] [ varchar ]（ 20 ）
）
- 在SQLServer的更高版本中，我可以在单个语句中执行此操作
insert into 示例 VALUES （' x'，' A'） - 注意不提供序列号的值
insert into 示例 VALUES （' Y'，' B'）
insert into 示例 VALUES （' z'， ' C'）
SELECT * FROM 示例生成输出
ID FLD1 FLD2
1 x A
2 Y B
3 z C 现在我们已经设置了数据，我创建了一个存储过程来重新序列化这个表（注意我没有尝试过这个通用的但是如果有的话，这是可能的。 winded，这样做）
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 示例 - 注意不包含ID
DROP 表示例
SELECT IDENTITY （ int ， 1 ， 1 ） as ID，* INTO 示例 FROM #RESERIAL ORDER BY ID
DROP TABLE #RESERIAL
END
GO 现在删除一条记录并添加一条新记录并看看那里有什么...
DELETE FROM 示例 WHERE FLD2 = ' B'
插入 进入示例 VALUES （< span class =code-string>' a'，' D'）
SELECT * FROM 示例我们得到
ID FLD1 FLD2
1 x A
4 a D
3 z C 但现在调用存储过程并比较结果
dbo.CHill60Reserialise
SELECT * FROM 示例给我们
ID FLD1 FLD2
1 x A
2 a D
3 z C
有几件事需要指出......这是一种非常低效的处理方式，所以我建议找一种方法将所有删除结合在一起（例如标记要清除的记录）并尽可能少地运行SP。
另外（根据解决方案2中的注释）这会受到在重新安排之前执行插入的影响，也可能受到顺序的影响来自。
最后 - 您在帖子中特别声明了SQL2000 ...请注意，从SQL2005开始，有更好的方法可以生成ROW_NUMBER或RANK - 这基本上是什么你试图在这里做
我认为你是在一组相关的TSQL语句之后，所有这些语句总结了上述所需的工作：
USE [cpqaAnswers]
如果EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N''[cpqa]。[tbl_ID_ProcessDCI]''）输入（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]''）并键入（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_ProcessDC IIdx]
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
* /
值得注意的是，主要是原始表的DROP。如果你反复这样做，那么索引表的DROP也是至关重要的。看中间步骤？不要被这两个不同的指数所迷惑，对吧？这就是为什么自动指数必须完全重组;正如解决方案01作者所暗示的那样，程序员真的受到IDENTITY机制的支配。最后，考虑操作ORDER BY ......这是该模型标量中的另一个潜在扳手。
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
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-- 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 EXAMPLEproduces the output
ID FLD1 FLD2 1 x A 2 Y B 3 z CNow 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)
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 GONow delete a record and add a new one and see what''s in there...
DELETE FROM EXAMPLE WHERE FLD2 = 'B' insert into EXAMPLE VALUES('a', 'D') SELECT * FROM EXAMPLEand we get
ID FLD1 FLD2 1 x A 4 a D 3 z CBut now call the stored procedure and compare the results
dbo.CHill60Reserialise SELECT * FROM EXAMPLEgives 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
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.
这篇关于在sql 2000中生成Seial Number的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
