Good day
I'm new to SQL.
i have a form in excel that the user will full in and send. on clicking send the stored proc pulls this info to a temp table.
I need that info to be moved to a new table that will not allow duplicate invoice numbers.
it needs to clear the temp table(not delete it just clear)
i need this to run after the first stored proc.
if anyone can point me to the right direction please...
Number of options here:
You can use IDENTITY. That will allocate the next available number to each new record inserted into the table. However, if a record is rolled back (e.g. some error, or deadlock, during INSERT) the number will be "consumed" and will not be allocated again. So the numbers will be unique but not necessarily contiguous.
You can do a query for MAX(MyInvoiceNoColumn) and then add one to make the next number. This is usually not a good idea because it blocks multiple people trying to insert new rows at the same time.
There are SEQUENCE numbers avail in recent versions of SQL - which might suit your need.
If you import your data using SSIS then you either need to assign the Invoice Number using some code in SSIS (which includes calling a Stored Procedure) or, alternatively, you could create a TRIGGER on the target table which automatically assigned the "next available number" to any newly inserted row (using whatever method, to calculate the "next number" that you decide). The benefit of this method is that ANYTHING that can insert into the table will automatically get the next-available-number, so you won't have that logic duplicated in multiple places. If you don't want this to "always" happen you could use a Stored Procedure instead - that would only apply when new rows were inserted using the Stored Procedure, so you could import data "in other ways" that did not use the Stored Procedure and therefore did not assign invoice numbers automatically.
I recommend that you create a UNIQUE INDEX on the Invoice Number column, that will ensure that there are no duplicates. If you need to allow some rows to have NO Invoice Number then you would need to use a Filtered Unique Index which excludes rows WHERE MyInvoiceNoColumn IS NULL