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,
- 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.