In order to batch process some inserts, I need to be able to use a function within the Insert statement, but I need the function to update a table that maintains the next available value (in this case, a barcode): INSERT Into SomeTable (name, docType, barcode) SELECT Name, DocType, fn_getNextBarcode FROM SomeOtherTable WHERE Criteria = DesiredCriteria where the function would update the barcode table so that the barcode is now increased by 1, then select and return that incremented barcode value. Yes, things would be easy if the vendor had auto incremented the barcode field, but they didn't, and we can't change it. Please help.
You can't update a base table from a function, unfortunately. This is going to prove a bit trickier. It is certainly possible to do this but a simpler alternative would get a temporary table of the barcodes first, and then assign them within the select statement.--all wrapped within a transaction.
Since your select statement might return more than one record, the fact that > You can't update a base table from a > function is actually a very good news as otherwise, it would allow you to update the base table as many times as there are records affected by the insert istead of doing it only once. This would be horrible. Here is the primitive sceleton of how you can do it. It should work if you have SQL Server 2005 or better. declare @current_code int; declare @final_code; begin tran; select @current_code = max(barcode_column) from dbo.Barcode; insert into SomeTable ([name], docType, barcode) select [name], DocType, @current_code + row_number() over (order by some_column) from SomeOtherTable where Criteria = DesiredCriteria; -- assuming that the number of affected rows matches the @@rowcount set @final_code = @@rowcount + @current_code + 1; update dbo.Barcode set barcode_column = @final_code; commit tran; go Oleg
I guess this can be achieved using trigger on insert. Try writing a trigger, which will read max value of barcode from the table into a variable, increment the variable and insert into table. This will behave very much similar to identity column as you will not have to mention the value of barcode in your select statement.