顺序发票编号SQL server

Question

 
IF EXISTS
   (SELECT *
    FROM   sys.objects
    WHERE  object_id = OBJECT_ID(N'[Imports].[Invoices_Numbers]')
           AND type IN ( N'U' ))
  DROP TABLE [Imports].[Invoices_Numbers]
结束
                

Answer 1

你的解决方案看起来有点复杂。除了数据导入任务之外，我不会使用set identity_insert重新注释。

为了确保唯一性，我将首先添加一个唯一约束：

alter table Invoices add constraint UX_Invoices_InvoiceNr unique

然后可以使用如下SQL语句插入发票：

while 1=1
    begin        
    declare @new_nr int
    select  @new_nr = max(InvoiceNr) + 1
    from    dbo.Invoices

    if @new_nr is null
        set @new_nr = 1

    insert  dbo.Invoices
            (InvoiceNr, ...)
    values  (@new_nr, ...)

    if @@rowcount = 1
        break
    end