I am importing data to an existing SQL Server table that has an int 4 field that must be unique, but cannot be changed to an auto-incrementing identity field. It will be a negative integer, that could grow into the thousands. As my INSERT query adds each row, I need to store the next (negative) sequential number in the int field. It would be great if one of my query's columns could be some kind of function, something like NextNumber(), that would always return the next number in sequence. I'm sure this would require storing the number in a one-record table, retrieving it, incrementing it, and saving it back to the table for the next use.
I followed the example given at the end of this Experts Exchange question:
This uses a stored procedure created as follows:
create proc dbo.getAndIncrease
declare @i int
update tbl set @i = col = col+1
I can get this to work in SQL Query Analyzer, but I don't know how to use it in an INSERT query as described above. I am an expert in Microsoft Access but a bit of a novice at some aspects of SQL Server, particularly when it comes to Stored Procedures and UDFs, so please give as much guidance about creation and usage as you can. Thanks!