SQLTeam.com | Weblogs | Forums

Is it possible to increment identity column during UPDATE statement?

#2

what does the schema definition of Employees look like?

#3

No, you cannot UPDATE or modify an identity value in any way, SQL won't allow it.

You'd have to delete the existing row (if you don't want one with the old identity value) and insert a new row with the desired identity value.

#4

Thanks for the prompt response Scott.

I am willing to change the field to non identity column.

This way, I can update the table and increment it like this:

   UPDATE Employees SET 
	    employeeName = @ename
   ,empTitle = @title
   ,email = @email
	   ,@employeeID = employeeID = @employeeID + 1
	  WHERE empID = @empID

This also means that inserting records will have to change to something like this:

BEGIN
 INSERT INTO Employees(employeeID,employeeName, empTitle, email, empID) Values (@employeeID+1,@ename, @title, @email, @empID)
 SET @employeeID = SCOPE_IDENTITY()
END

However, I am getting an error that I cannot insert NULLS into Employees table even though I removed primary key attachment to the field and also changed identity seed to No.

Any ideas what I need to change in both the insert and update statements?

#5

If you're confident that will work, we can do it. But just adding 1 to what was an identity column will duplicate another identity unless your increment was not set to 1 (which seems likely here, just thought I'd be sure).

The UPDATE should be fine.

The INSERT will be trickier, since employeeID will no longer be automatically assigned. You'd want an index on employeeID. Note that we want to get the max value as part of the INSERT itself so it's (almost) guaranteed not to increase while it's being INSERTed. If we read it first in a separate statement, then two different tasks could get the same max value and try to assign the same employeeID:

IF OBJECT_ID('tempdb.dbo.#Employees') IS NOT NULL
    DROP TABLE #Employees
CREATE TABLE #Employees ( employeeID int NOT NULL)
INSERT INTO Employees(employeeID,employeeName, empTitle, email, empID) 
OUTPUT INSERTED.employeeID INTO #Employees
SELECT ca1.employeeID_max,@ename, @title, @email, @empID
FROM Employees
CROSS APPLY (
    SELECT MAX(EmployeeID) AS EmployeeID_Max
    FROM Employees
) AS ca1

SELECT TOP (1) @employeeId = employeeID
FROM #Employees
#6

Oh WOW, this is awesome.

Thank you very much Scott.

Do I need entire code or will this part below just be enough to do the trick?

INSERT INTO Employees(employeeID,employeeName, empTitle, email, empID) 
OUTPUT INSERTED.employeeID INTO #Employees
SELECT ca1.employeeID_max,@ename, @title, @email, @empID
FROM Employees
CROSS APPLY (
    SELECT MAX(EmployeeID) AS EmployeeID_Max
    FROM Employees
) AS ca1

SELECT TOP (1) @employeeId = employeeID
FROM #Employees
#7

That should be the equivalent code for the INSERT part (based on the code given here). The UPDATE is still separate, of course.

#8

Ok, great.

Thanks so much for your expertise, assistance and time.

It really helped and I have learned a lot

#9

Hey Scott,

I realize the solution won't work for me.

First of all, it is updating an existing identity key.

The updating is not a problem as it does the update successfully.

What is a problem is that I do not want the identity key updated just like I mentione in my post.

Rather, I want it incremented.

For instance, asume that I have a value of 123, after an update, I will like a new value created so we have 123 and 124 so that when records are submitted to other related tables, the new records for those related tables will have value of 124.

I realize now that even if this were to be possible, the newly created value will have no associated employeeName, title, email, etc.

This defeats the purpose.

So, what I think is plausible now, even though it isn't the best solution, is to remove the update portion of the code and create duplicate values but with new value for identity key.

This way, since I have a date table, i can join with current year to get the most recently added record.

Once the submission period is over, I can clean the table and remove duplicates.

Not an ideal solution but I can't think of a better solution right now.

Thanks a lot for all your help.

#10

Could you please explain your requirements specifically what is the reason you want to do this identity update? Sounds fraught with danger

#11

Hi,

yosiasz

We have about 10 tables altogether.

One of those tables is called Employees with identity key of EmployeeID.

9 of those tables are related to Employees table by EmployeeID.

Each year, employees are required to submit some sort of disclaimer filled with several other form fields.

The web appp went LIVE last year.

The requirement is that when you load the app and it is your firs time submitting it, the form is blank and you can feel your personal information and then rest of form and then submit.

If you have loaded the app and it turns out that you had submitted it the previous year, your personal information is automatically filled and then you complete rest of the app.

Once the app is submitted, and to query your data for the current year, your employeeID is join to other tables including date table which stores date information that is used to determine whether you had submitted this before or not.

The issue we are having here is that when an employee who had submitted this form the previous year attempts to submit it for the current year, we are getting error that DNULL is not valid for integer value.

Only integer that could be causing this problem is EmployeeID which relates all tables to Employees table.

Although using COALESCE is useless in this case, even using it does not stop the error.

So, what I noticed is that if I remove the UPDATE portion of the stored procedure, everything works.

With UPDATE portion included, the error persits.

So I believe the SP is the source of the error which is why I am trying to obtain a new ID that will be used to related to other tables.

Hope this explanation helps.

Thank you very much.

#13

The keyword here is "that could be causing this issue" not sure you want to do all of this based on that one assumption.
Please post the sproc that you are using that I'd causing this issue

#14

I hope it is not my post that is being deleted.

I did not withdraw any post.
Thanks Jeff

#15

No... I didn't withdraw your post. I withdrew mine because I asked "Why" you wanted to do this and realized you had already explained why. I also for not getting back to this because I ran out of time (work is particularly demanding lately).

Contrary to what Scott stated, it's pretty easy to update an IDENTITY column but it's absolutely the wrong solution for what you're trying to do because, as he pointed out, it brings a set of problems to the table that will cause you great pain forever for this type of thing.

Having read the "Why" that you posted, I believe that you've fallen into the trap that so many have in the past and, with absolutely no malice or insult intended, you've settled on a solution for a problem and have defined how you want to solve it and your solution (in my humble opinion) is wrong. It's not your fault because you simply don't know what you don't know. Heh... like I said, no insult intended... we all have the same problem and "we" includes me.

Getting to your problem, it sounds to me like you want (or should) to do two things...

  1. Keep track of changes.
  2. Easily find the latest, most up to date row for each Employee.

The simple answer to that problem is to use a "Temporal Table". You won't even need to join to a date table or other such things. And remember I talked about "we" including me when it comes to things we don't know we don't know? Yeah... I knew nothing of Temporal Tables 2 weeks ago except that they existed.

Temporal tables contain both an active table and a history table and they're as easy to use as it is to fall off a slippery rock. To find the most current update, you only need the Employee ID and the current date/time. Your select would look something like the following.

--===== Find the latest info for employee ID "nnn"
 SELECT list-of-columns 
   FROM dbo.Employee   
    FOR SYSTEM_TIME    
        BETWEEN StartDT AND EndDT --These names are defined by you
  WHERE Employee_ID = nnn
;

What about INSERTs, UPDATEs, and DELETEs? The way you're trying to do things, this will become a huge headache. With Temporal Tables, that slippery rock level of ease comes into play again. You don't need to write any triggers nor worry about updating some "sequential number" or anything else. It's all "auto-magically" taken care of for you and that includes setting the StartDt and EndDt. The system will auto-magically assign an EndDt (or whatever you decide the name of the column will be) of 9999-12-31 23:59:59.9999999 to the latest row and that's why the simple SELECT query works nicely above. When you update the table for the given employee, it'll auto-magically change the EndDt to the current date, move the row to the history table, and update the current row.

Here's the Google link for more information. Unfortunately, you've not stated which version of SQL server you're using. Temporal Tables became a thing as of 2016. If you have less than 2016, you can do the same thing except you'll need to write your own triggers, etc, and that's not that difficult. That's what I used to do prior to 2016.
https://www.google.com/search?q=temporal+tables+sql+server

#16

Thanks a bunch Jeff.

Yes, we use 2016 instance of sql server.

I will give this temporal table ago.

Thanks ago.

#17

So how exactly do you UPDATE an identity value? SQL simply doesn't allow it.

You and DELETE and re-INSERT a row, but you can't UPDATE an identity value, afaik.

What are you doing that allows you to actually UPDATE an identity value?

#18

SET IDENITY_INSERT schemaname.tablename ON;

It can only be activated on 1 table at a time and you must remember to turn it back off.

To emphasize the great point you made before, this is normally a terrible idea and generally is an indication of a bad design or simply a bad idea.

#19

Hi Scott,

First of all, I have tried as many times as possible to explain that I am NOT doing an UPDATE.

Maybe, I am not explaining well.

All we are trying to do is avoid having repetitive employee names on same Employee table.

Instead, If the name already exists on that table, update it.

However, we are trying to see if employee info can be updated if exists rather than create another one AND then see if it is possible to INCREMENT the existing identity so we can use that new identity keep and insert it into other tables.

To answer your question about what I am doing that allows me to increment, not update identity value?

Please see the explanation I provided here:

Thank you

#20

Just to be sure, you're saying you want to change the EmployeeID??? I'm just not understanding the problem correctly here. Which identity are you talking about incrementing???

#21

Hi Jeff,

It seems to me that I am confusing you guys with our requirements.

If so, I sincerely apologize.

I also thank you guys for your patience.

Quite frankly, I don't even think what I am asking is possible. I am just hoping for a better approach.

So, please allow me to try explaning it again.

As stated previously, we have 10 tables, including the Employees table.

All these tables are related by EmployeeID, PK , identity seed for Employees table.

There is another important table worth mentioning here, dateDetails table.

This table contains dates stored when an employee submits a record.

So, as it is now, our management mandates employees to take an avidavit that lists their sources of incomes other than their current employment.

Even if your source of income is your current employment, you are still required to fill this form.

If the employee has a spouse, s/he must also disclose spouses income. (Stupid if you ask me because it is not their business where spouse's income comes from but it is what it is).

This avidavit must be taken every year.

That's why we have dateDetails table to keep track of the year(s) an employee has completed this form.

So, in our query, we join dateDetails table with Employees table on EmployeeID and current year if we want to know the year form is completed.

So far clear, I hope.

Where it gets dicey for us is that after each form submission, we grab the last inserted key of the Employees table.

Using this key, we insert records into the rest of the tables including the dateDetails table.

So, asume that employee Joe Blo completed this form in 2018, when he loads the app, enters a unique employee number (diferent from EmployeeID), the app is able to recognize that Joe Blo completed this form in 2018 and then loads his personal information like name, title, email, phone and that unique ID.

Joe Blo then goes ahead to complete the rest of form vis-a-vis, if he has another source of income or same as last year, if he was single last year and got married this year, if the wife's income source has changed, etc.

To do this, a new EmployeeID needs to be generated.

If we do an UPDATE statement like we did on the stored proc posted here, no new employeeID will be generated since it is just an update.

When this happens, only Employees table gets updated. However, new records need to be inserted the other tables and this generates an error that "DBNULL conversion to integer is invalid"

I believe the reason for the error is that the app is looking for a new employeeID to be inserted into the tables but is not finding any.

That led to my question as to whether the existing identity seed in Employees table can be incremented so that if the existing id is say 2365, then a new one, 2366 will be generated and then used for inserting into other tables.

Otherwise, we will just have to insert employees info all over again into Employees table just to grab the latest inserted ID.

IT appears that the db design is flawed because inserting same information over and over again into Employees table creates anomalies even though we can obtain the most recently inserted records by joining Employees table with dateDetails table to obtain current year.

So, I guess my question is that if it is not possible to increment identity seed, any ideas how to redesign the table so only Empoyee info gets inserted into Employees table just once and Employee IDs can be inserted into another table once a new record is created?

This way, we join that table with Employees table and the rest of the tables to get the most recently inserted records to display the employees.

I hope this a bit clearer?

#22

Typically you'd always use the same EmployeeID for any given employee. New rows in other tables would simply reference the original EmployeeID.

If you wanted to, you could update the Emp row with the last insert/update that occurred in any table for that emp, just as a reference, but you would not assign a new EmpID. There's one emp, only, taking different actions, not multiple Emps.