reset table identity in TSQL

I have a table set up with a primary key identity column. Most of the time
this works fine, but there are occasions where I need to update a record and
set the primary key to a number that is not in the identity sequence. For
instance the identity value is 160001 and I need to manually update a
record's primary key to 5000.
So far the only way I know how to do this is to go into Enterprise manager
and select identity to NO on the identity field to save the table, run my
update queries, go back into Enterprise manager and set identity back to YES
and resave. I am sure there must be a way I can put this process into a
script. Is there are way to do this in TSQL without having to use Enterprise
Manager?
Example:
Identity primary key customer_id 160001 needs to be updated to 5000 and
afterwards restore identity back to 160001
thanks,
FrankLookup DBCC CHECKIDENT in SQL Server Books Online.
--
- Anith
( Please reply to newsgroups only )|||look up SET IDENTITY_INSERT in BOL
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||SET IDENTITY_INSERT tableName ON
GO
UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
GO
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
Why? Should you really be using a surrogate if people care what the actual
value is?
In any case, see the following topics in Books Online:
SET IDENTITY_INSERT
DBCC CHECKIDENT
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave.
Please stop using Enterprise Manager to manipulate data. You can do
irrepairable damage, or a whole lot of unnecessary locking, e.g. when the
tool goes behind the scenes and makes a complete copy of a 4 billion row
table without warning you first (many of EM's shortcuts are accomplished
exactly this way).
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
Here's what my approach would be (though I still fail to see the purpose of
assigning some known, meaningful value to a surrogate).
CREATE TABLE blat
(
blatID INT IDENTITY(160000, 1),
cust VARCHAR(32)
)
GO
SET NOCOUNT ON
INSERT blat(cust) VALUES('lobster')
INSERT blat(cust) VALUES('shrimp')
GO
DBCC CHECKIDENT('blat', RESEED, 4999)
GO
INSERT blat SELECT cust FROM blat WHERE blatID = 160001
GO
DELETE blat WHERE blatID = 160001
GO
DBCC CHECKIDENT('blat', 'RESEED', 160000)
GO
INSERT blat(cust) VALUES('squid')
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
Please try this
declare @.i int
declare @.c1 nvarchar(100)
set @.i = 5000
set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
ltrim(rtrim(convert(char,@.i)))+')'
exec sp_executesql @.c1
After updating
declare @.i int
declare @.c1 nvarchar(100)
set @.i = 160001
set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
ltrim(rtrim(convert(char,@.i)))+')'
exec sp_executesql @.c1
Thanks
Hari
MCDBA
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||> SET IDENTITY_INSERT tableName ON
> GO
> UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> GO
This was my initial angle, but regardless of IDENTITY_INSERT setting, I get:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'customer_id'.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Can I put this into a transaction?
begin transaction
set identity_insert tblName ON
UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
commit transaction
Would that work?
thanks,
Frank
"Raymond D'Anjou (raydan)" <raydan@.canatrade.nospamcom> wrote in message
news:ub8z#LavDHA.560@.TK2MSFTNGP11.phx.gbl...
> SET IDENTITY_INSERT tableName ON
> GO
> UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> GO
> "Frank" <frankenberryz@.yahoo.com> wrote in message
> news:eY0Bb.6$1i.21421@.news.uswest.net...
> > I have a table set up with a primary key identity column. Most of the
time
> > this works fine, but there are occasions where I need to update a record
> and
> > set the primary key to a number that is not in the identity sequence.
For
> > instance the identity value is 160001 and I need to manually update a
> > record's primary key to 5000.
> >
> > So far the only way I know how to do this is to go into Enterprise
manager
> > and select identity to NO on the identity field to save the table, run
my
> > update queries, go back into Enterprise manager and set identity back to
> YES
> > and resave. I am sure there must be a way I can put this process into a
> > script. Is there are way to do this in TSQL without having to use
> Enterprise
> > Manager?
> >
> > Example:
> > Identity primary key customer_id 160001 needs to be updated to 5000 and
> > afterwards restore identity back to 160001
> >
> > thanks,
> > Frank
> >
> >
>|||I was thinking that maybe IDENTITY_INSERT doesn't work for updates after I
posted the message.
BOL only mentions that it works for updates.
Sorry, should have put "Untested" before my solution.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eaiPsNavDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > SET IDENTITY_INSERT tableName ON
> > GO
> >
> > UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> > GO
> This was my initial angle, but regardless of IDENTITY_INSERT setting, I
get:
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'customer_id'.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||I get the error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'customer_id'
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#v45XNavDHA.560@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Please try this
> declare @.i int
> declare @.c1 nvarchar(100)
> set @.i = 5000
> set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
> ltrim(rtrim(convert(char,@.i)))+')'
> exec sp_executesql @.c1
> After updating
> declare @.i int
> declare @.c1 nvarchar(100)
> set @.i = 160001
> set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
> ltrim(rtrim(convert(char,@.i)))+')'
> exec sp_executesql @.c1
>
>
> Thanks
> Hari
> MCDBA
>
> "Frank" <frankenberryz@.yahoo.com> wrote in message
> news:eY0Bb.6$1i.21421@.news.uswest.net...
> > I have a table set up with a primary key identity column. Most of the
time
> > this works fine, but there are occasions where I need to update a record
> and
> > set the primary key to a number that is not in the identity sequence.
For
> > instance the identity value is 160001 and I need to manually update a
> > record's primary key to 5000.
> >
> > So far the only way I know how to do this is to go into Enterprise
manager
> > and select identity to NO on the identity field to save the table, run
my
> > update queries, go back into Enterprise manager and set identity back to
> YES
> > and resave. I am sure there must be a way I can put this process into a
> > script. Is there are way to do this in TSQL without having to use
> Enterprise
> > Manager?
> >
> > Example:
> > Identity primary key customer_id 160001 needs to be updated to 5000 and
> > afterwards restore identity back to 160001
> >
> > thanks,
> > Frank
> >
> >
>|||Step 1: Make an INSERT script for the row with PK value 5000.
Step 2: Delete the existing row with PK value 160001
Step 3: Set IDENTITY_INSERT for the table ON
Step 4: Run the INSERT script for PK 5000
Step 5: Set IDENTITY_INSERT for the table OFF
Step 6: Run DBCC CHECKIDENT(<tablename>, RESEED, 16000)
Hope this helps.