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.
posted by trunk muffins to Computers & Internet (7 answers total)
With that many rows, I'd go with a cursor over a temp table. Here's a rough sketch of an example.

SET @max_id = (SELECT MAX(id) FROM source_table)

SELECT columns, to, copy
FROM source_table

OPEN cursor_name

FETCH NEXT FROM cursor_name
INTO @columns, @to, @copy

INSERT INTO #new_table

SET @max_id = @max_id + 1


posted by purephase at 11:29 AM on August 3, 2010

Is your goal to insert new records into the destination table or update records that are already there or both? After the initial update, is this likely to happen in batches of 20 or one at a time?
posted by Jugwine at 11:37 AM on August 3, 2010

Create your temp table with an identity column:
     create table temp_table( id int not null identity, col1 ... ;
Insert your data in your temp table; each now has a unique id (starting from 1):
     insert into temp_table (col1, col2 ) 
     select col1, col2 
     from src_table;
    insert into dest_table (id, col1, col2, ...) 
    select id + ( select max(id) from dest_table ), 
      col1, col2 .... 
    from temp_table;
Then drop your temp table until next time (you need to drop it to reset the identity column).
posted by orthogonality at 12:51 PM on August 3, 2010

Just out of curiosity, what's the advantage of using a temp table over something like this:

declare @max_id int
select @max_id = max(id) from dest_table

insert into dest_table (id, col1, col2, ...)
   select @max_id + row_number() over(order by id),
   col1, col2 ....
   from source_table;

I'm not the SQL veteran that orthogonality is, so I'm curious how these two methods compare.
posted by DakotaPaul at 4:56 PM on August 3, 2010

DakotaPaul: your answer is clearly better, if table `source_table` has ids, and if there's a decent way to filter out already transfered rows (e.g., `where create_date >= @last_time_we_updated`).

(Mea culpa: once I confirmed that SQLServer doesn't have sequences (which is the "real answer"), I just assumed that analytic functions were also not available. (I've been pure Oracle for the last year, and am clearly out of date on SQLServer.))
posted by orthogonality at 8:33 PM on August 3, 2010

Thanks so much -- awesome suggestions, and I'll tinker around with these today.

If anyone is still reading and wants to chime in: I will only be inserting new records in the destination table, never updating. Also, the ID I am concerned with exists only in the destination table, not in the source table.
posted by trunk muffins at 7:22 AM on August 4, 2010

DakotaPaul: your answer is clearly better, if table `source_table` has ids...

It should still work even if there are no IDs. You could do row_number() over(order by any_column), and I don't think it'll matter because it just needs to order by something, whether it's unique or not.
posted by DakotaPaul at 12:44 PM on August 4, 2010

« Older Oh, the places I'll go...   |   Lemon Curry? Newer »
This thread is closed to new comments.