I am attempting to do an place into a recognised table with a primary key fields and the other area (refer to it as field1) that's unique (this other unique area includes a unique constraint stopping my card inserts). Field1 isn't a name area, so it doesn't autonumber. Regrettably I can not alter the table. Existing card inserts are created using code to increment and all sorts of involve looping/cursors. Something similar to Choose MAX(field1) + 1

So, can there be anyway to get this done place without looping/cursor? This area means absolutely nothing to me, but you will find already 500,000+ records utilizing their silly numbering plan, and so i must respect that.

This really is simplified (ReceiptNumber may be the area I wish to place unique), but:

SET XACT_ABORT ON

Begin Transaction TransMain
Declare @nvErrMsg nvarchar(4000)

--Insert inventory receipts
Insert Into Avanti_InventoryReceipts ( 
    ReceiptNumber , ItemNumber , ReceiptDate , OrderNumber , JobNumber , Supplier ,
    LineNumber , MultiLineNumber , [Status] , QtyOrdered , QtyReceived , QtyToReceive ,
    QtyBackOrdered , Cost , Wholesale , LastCost , QtyToInvoice , QtyUsed ,
    ReferenceNumber , [Description] , SupplierType , Processed , DateExpected , DateReceived , 
    AccountNumber , Reference2 , EmployeeCode , ExtraCode , Location , RollNumber , 
    QtyIssues , Notes , NumPackages , BundleSize , ConsignmentUnitPrice , RecFromProduction , 
    QtyCommitted )
SELECT ( SELECT MAX(ReceiptNumber) + 1 FROM Avanti_inventoryReceipts ) , CR.ItemNumber , Convert(char(8), GETDATE(), 112) , PONum , 'FL-INV' , PH.POVendor ,
    0 , 0 , 'O' , CR.QtyOrdered , QtyReceivedToday , QtyReceivedToday ,
    Case @closePO 
         When 'N' Then Case When ( QtyOrdered - QtyReceivedToday ) < 0 Then 0 Else ( QtyOrdered - QtyReceivedToday) End 
         When 'Y' Then 0
         Else 0 End     
          , PD.TransCost * QtyReceivedToday , IH.PriceWholeSale , IH.CostLast , QtyReceivedToday , 0 ,
    '' , PODetailDescription , '' , '' , '' , Convert(char(8), GETDATE(), 112) , 
    '' , '' , @employeeCode , '' , 'F L E X O' , '' , 
    0 , 'Flexo Materials' , 0 , 0 , 0 , '' , 0
FROM FI_CurrentReceiptData CR
LEFT JOIN Avanti_PODetails PD ON CR.PONum = PD.PONumber
LEFT JOIN Avanti_POHeader PH ON CR.PONum = PH.PONumber
LEFT JOIN Avanti_InventoryHeader IH ON CR.ItemNumber = IH.ItemNumber


  IF @@ERROR <> 0 
    Begin
      Select @nvErrMsg = 'Error entering into [InventoryReceipts] -' +  [description]
        From master..sysmessages
     Where [error] = @@ERROR

     RAISERROR ( @nvErrMsg , 16, 1 )
     Goto Err_
   End

  Commit Transaction TransMain
  Goto Exit_


Err_:

   Rollback Transaction TransMain

Exit_:

SET XACT_ABORT OFF

You could do this this:

insert into mytable (field1, field2, ...)
values (( SELECT MAX(field1) + 1 from mytable), 'value2', ...);

Why don't you looping? It ought to be quite efficient.

Since you have a distinctive constraint around the area, you are able to:

  • Simply attempt to place MAX(field1) + 1. Since there's index on UNIQUE area, MAX is fast.
  • If it is passes, great you're done.
  • Whether it fails (that will typically be manifested being an exception inside your client code), just repeat the process before you succeed.

More often than not, the Place will succeed immediately. In rare instances in which a concurrent user attempts to place exactly the same value, you'll handle that beautifully if you attempt the "next" value.

I added an autonumber beginning from in client code and passed that in. Now I am adding that value towards the max receiptnumber to obtain a unique one. Also, I recognized I already had a name column in FI_CurrentReceiptData, however i did not desire to use that certain since it will not start at for every receipt set, and reseeding the identity every time appears like a total waste of processor time.