How to manually increment an integer value in a SQL Server 2005 database table?
August 3, 2010 11:10 AM Subscribe
How would you handle "manually" incrementing the value of this integer field in SQL Server 2005?
I need to update one database table with records from another table, both in SQL Server 2005. I'm planning to do this with a stored procedure. This will replace the current update process, which is handled by a Visual Basic application that retrieves new data from a flat file.
Problem: one of the fields in the destination table that comprises the primary key is currently being populated with an integer value that's calculated by the VB application. Ick.
I didn't design any of this, but it's what I have to work with. I cannot alter the destination table.
Off the top of my head, here is what I have come up with:
- Populate a temp table with the records from Source Table that will eventually go into Destination Table, with an int field left empty for the moment.
- Declare an @id int variable initialized to the current max value in the Destination Table
- Update the empty int field in Temp Table with incrementing values of @id
- Finally, update Destination Table with all rows in Temp Table.
There has to be a more straightforward (and less processor-intensive) way to handle the situation, right? I've not worked with cursors before, and maybe that's the answer. How would you handle this stupid situation in a non-stupid way?
If it's relevant: once this is put in place, it'll probably be moving an average of 20 records a day into the destination table, but the initial move will probably be a few thousand records.