如何在SQL中自动增量 [英] How to autoincrement in SQL

问题描述

如果点击按钮自动增加WR001然后自动增加

并插入表格

推荐答案

Google？ :(（



使用SQL Server自定义自动生成的序列 [ ^ ]

Google? :((

Custom Auto-Generated Sequences with SQL Server[^]

没有内置机制，



a建议：你可以创建一个数字列，其中标识为 true ，并创建一个复合列，其中包含数字列和'WR'的组合前缀，只需确保在插入时添加数字列。

there is no inbuilt mechanism,

a suggestion: you can create a numeric column with Identity as true, and create a composite column with composition of the numeric column and 'WR' prefix, just make sure while inserting you add in numeric column.

这为您提供了一些线索：

This gives you some clues :

declare @ceiling int
set @ceiling =1000

declare @s varchar(50)
select @s = 'WR001'

select 'WR' + SUBSTRING( cast(@ceiling + CAST(SUBSTRING(@s , 3 , LEN(@s) - 2 ) as int)  + 1 as varchar) , 2 , len(cast(@ceiling as varchar))-1)

我不知道您的表名和值'WR001'的字段名称所以我只是将它分配给@s，但你应该总是得到最大的一个并把它放在@s变量中。

如果你想要超过1000个ID，那么改变@ceiling值。



[评论后编辑]



请创建这样的程序并更改输入参数以涵盖所有表格字段。

I didn't know your table name and field name of the value 'WR001' so I just assigned it to @s but you should always get the largest one and put it in the @s variable.
And if you want more than 1000 IDs then change the @ceiling value.



Please create a procedure like this and change the input parameters to cover all of your table fields.

create procedure InsertNewOrder
@t as varchar(100)
as
begin

set transaction isolation level serializable

BEGIN TRANSACTION

declare @newid as varchar(50)
declare @ceiling int
set @ceiling =1000
declare @s varchar(50)
select top 1 @s = iddentity  from tblorder order by iddentity desc
set @s = ISNULL( @s , 'WR000');
select @newid = 'WR' + SUBSTRING( cast(@ceiling + CAST(SUBSTRING(@s , 3 , LEN(@s) - 2 ) as int)  + 1 as varchar) , 2 , len(cast(@ceiling as varchar))-1)

insert into tblorder(iddentity, title) values(@newid, @t)

COMMIT TRANSACTION

end

用于插入新数据调用它像这样：

for inserting new data call it like this :

InsertNewOrder 'order name'

希望它有帮助。

Hope It Helps.

这篇关于如何在SQL中自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！