Issues in Identity Column
-
Dear all,
In one table, i have identity column , how can we update the values in identity column,
If inserting mean we can use identity_insert on then Off
But In update How can we update ?
Error Is:
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column Column_name
-
You can't update them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You can't update an identity row, and you shouldn't. Nor should you need to.
Are you trying to put row numbers on something? If so, either use the Row_Number() function in a query, or, better yet, let the presentation layer put row numbers on for people to look at.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Saravanan_tvr (11/12/2010)
Dear all,In one table, i have identity column , how can we update the values in identity column,
If inserting mean we can use identity_insert on then Off
But In update How can we update ?
Error Is:
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column Column_name
Hmmm.... as the others have said, there's normally no need to keep IDENTITY's perfectly sequential over time. Still, we don't know why you may need to do this. Perhaps if you explained the larger problem a bit more we may be able to help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Saravanan_tvr (11/12/2010)
...how can we update the values in identity column?Delete the row and then re-insert it with the correct identity value specified explicitly.
You will need SET IDENTITY_INSERT ON for that table for the insert part to work.
Do the delete and the insert inside a transaction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White NZ (11/14/2010)
Saravanan_tvr (11/12/2010)
...how can we update the values in identity column?Delete the row and then re-insert it with the correct identity value specified explicitly.
You will need SET IDENTITY_INSERT ON for that table for the insert part to work.
Do the delete and the insert inside a transaction.
If you use OUTPUT INTO in the delete, the insert will be part of the same transaction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (11/15/2010)
If you use OUTPUT INTO in the delete, the insert will be part of the same transaction.How do you know the target table does not have enabled triggers, check constraints, or enabled rules, and that it does not participate in a foreign key relationship? All those would cause the OUTPUT to fail.
BEGIN TRAN;
DELETE
INSERT
ROLLBACK/COMMIT;
Sometimes simple is best.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White NZ (11/14/2010)
Saravanan_tvr (11/12/2010)
...how can we update the values in identity column?Delete the row and then re-insert it with the correct identity value specified explicitly.
You will need SET IDENTITY_INSERT ON for that table for the insert part to work.
Do the delete and the insert inside a transaction.
You may wish to check for cascading deletes before trying this.
Hope this helps,
Rich[p]
[/p] -
RichardDouglas (11/15/2010)
Paul White NZ (11/14/2010)
Saravanan_tvr (11/12/2010)
...how can we update the values in identity column?Delete the row and then re-insert it with the correct identity value specified explicitly.
You will need SET IDENTITY_INSERT ON for that table for the insert part to work.
Do the delete and the insert inside a transaction.
You may wish to check for cascading deletes before trying this.
Good point. Triggers too.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White NZ (11/15/2010)
GSquared (11/15/2010)
If you use OUTPUT INTO in the delete, the insert will be part of the same transaction.How do you know the target table does not have enabled triggers, check constraints, or enabled rules, and that it does not participate in a foreign key relationship? All those would cause the OUTPUT to fail.
BEGIN TRAN;
DELETE
INSERT
ROLLBACK/COMMIT;
Sometimes simple is best.
In what possible scenario would that succeed where an Output Into would fail? They both do exactly the same thing, in exactly the same table. Anything that would cause one to fail should cause the other to fail too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (11/16/2010)
In what possible scenario would that succeed where an Output Into would fail? They both do exactly the same thing, in exactly the same table. Anything that would cause one to fail should cause the other to fail too.I was referring to this...
Books Online (OUTPUT Clause (Transact-SQL)):
<OUTPUT_CLAUSE> ::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT <dml_select_list> ]
}
output_table cannot:
Have enabled triggers defined on it.
Participate on either side of a FOREIGN KEY constraint.
Have CHECK constraints or enabled rules.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Ah. That makes sense then. Forgot about those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (11/16/2010)
Ah. That makes sense then. Forgot about those.No worries.
For anyone else that wants to play around, here's a repro:
SET XACT_ABORT ON;
GO
CREATE TABLE #ToModify
(
row_id INTEGER IDENTITY(10, 1) PRIMARY KEY,
some_data INTEGER NOT NULL CHECK (some_data > 0),
);
INSERT #ToModify (some_data) VALUES (10);
INSERT #ToModify (some_data) VALUES (30);
INSERT #ToModify (some_data) VALUES (50);
SET IDENTITY_INSERT
#ToModify ON;
BEGIN TRANSACTION;
INSERT #ToModify (row_id, some_data)
SELECT 9, TM.some_data
FROM #ToModify TM
WHERE TM.row_id = 10;
DELETE #ToModify
WHERE row_id = 10;
COMMIT TRANSACTION;
--Msg 333, Level 16, State 1, Line 1
--The target table '#ToModify' of the OUTPUT INTO clause
--cannot have any enabled check constraints or any enabled rules
--Found check constraint or rule 'CK__#ToModify__some___678A2F1F'.
DELETE #ToModify
OUTPUT 9, deleted.some_data
INTO #ToModify (row_id, some_data)
WHERE row_id = 11;
--Msg 5328, Level 16, State 1, Line 1
--Cannot insert explicit value for the identity column 'row_id'
--in the target table '#ToModify' of the INSERT statement when the
--FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
INSERT #ToModify
(row_id, some_data)
SELECT 9, D.some_data
FROM (
DELETE #ToModify
OUTPUT deleted.some_data
WHERE row_id = 11
) D;
SET IDENTITY_INSERT
#ToModify OFF;
GO
DROP TABLE
#ToModify;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply