Incrementing Column Value by "1"

[luvmy2Js]

Is there a way to tell SQL to get the max(ID) and add "1" to it where the value is NULL? But what I need is if there is more than one NULL value increase in increments of "1".

Is there an easy way to accomplish this? Sample data is below. Column "A" has the NULL value in it. We couldn't make this an identity column b/c when changes are being made and sent back in, they want to keep the same value for column "A"..

Thanks a million!!!

A B C D E F G H I J K L M N O
2741 501304 Joe Spellman 32.38 % Monthly USD Rice CO01 LA 9/24/2010 O
NULL 501304 Joe Spellman 45.15 % Monthly COP Travel CO01 LA Change Correction 9/25/2010 N

 

[cdck]

I'm not sure I understand the concern with making the column an int identity column with an increment of 1... the identity column would not be affected by changes to the data unless one of the columns specifically changed was your identity column.

It is my undertsanding that if you were using a timestamp column as the key, then that column would be changed each time the record was updated.

But an int column set as identity remains static unless specifically changed.

Cheryl dc Kern

 

[luvmy2Js]

The issue with an identity column is when the file comes back and that column id = 2417, they will send the same column back with the changes made and the id = 2417. When it is an identity column, that ID value will be overriden and I won't know what row it is taking the place of.?

 

[cdck]

So you are exporting the data into some outside document which your users are editing and returning, do I have this right?

If so, when the data returns, you should use an update query to pull the changed data and update the original record based on the ID column, in which case the identity column would not get overwritten. You would then append only those records in which the identity column was null, which would allow SQL to automatically assign the values to those records, which would be pulled out with them the next time.

If I am still not understanding what you are trying to do, could you explain more fully? If I don't understand, them I'm also not sure where the process you described above would happen, so I'd need to get that to try to answer.

Cheryl dc Kern

 

[TheBugSlayer]

Luvmy2Js,

Modify the code below to suit your needs. I used a DMV for illustration purposes:

DECLARE @maxid INT
DECLARE @nullcnt INT
SELECT @nullcnt = count(*) FROM sys.dm_exec_sessions WHERE session_id BETWEEN 11 AND 23
SELECT @maxid= max(session_id) + 1 FROM sys.dm_exec_sessions;
--SELECT @maxid, @nullcnt;

WITH session_cte(new_id)
AS
(
	SELECT @maxid AS new_id
    UNION ALL
    SELECT new_id + 1 FROM session_cte 
    where  new_id + 1  <  @maxid + @nullcnt
)
SELECT * FROM
(
	SELECT new_id, login_time, (ROW_NUMBER() OVER (ORDER BY new_id)) AS rank FROM session_cte, sys.dm_exec_sessions 
	WHERE session_id BETWEEN 11 AND 23
) A WHERE rank % @nullcnt = 0

You can easily change the SELECT statement to an UPDATE statement but I am not sure how you uniquely identify the rows in your table.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)