Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 12th, 2005, 11:37 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default Identity column

Hello all,

   Is there a way to renumber the values in an identity column? Ex. If I had a table where some rows where deleted and had these values

ID Value
-- -----
1 Black
3 Blue
7 Green
10 White

Is there something I can do to have the table look like this?:
ID Value
-- -----
1 Black
2 Blue
3 Green
4 White

Thanks,

Jim
 
Old April 12th, 2005, 01:07 PM
Authorized User
 
Join Date: Mar 2005
Location: , , India.
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

there is command in sql server

set identity_insert on

you can executing this command
after that u can set the value of ID column in the table

Thanks

Atul

 
Old April 12th, 2005, 01:18 PM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Agree with Atul..also search for DBCC CHECKIDENT in books online


example from BOL

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO

SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO
 
Old April 12th, 2005, 01:18 PM
Authorized User
 
Join Date: Mar 2005
Location: , , India.
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi jim,

if you want that table is automatically corrected.
then you will have use a cursor for that

simply make a cursor
script shoud be like this

declare @var int
declare @id int

set @var = 1

declare serialCursor cursor dynamic for select ID from DesiredTable order by ID for update
open serialCursor
fetch next from serialCursor into @id
while @@fetch_status=0
    begin

        update DesiredTable
        set @var = @var+1 where current of serialCursor
    fetch next from serialCursor into @id
end
close serialCursor
deallocate serialCursor

thanks
atul

 
Old April 12th, 2005, 01:36 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Thanks all for you help
What I did was to remove the identity column then create it again.

thanks for the replys!!
 
Old April 12th, 2005, 02:01 PM
Authorized User
 
Join Date: Mar 2005
Location: , , India.
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default


yes

that will also work

atul





Similar Threads
Thread Thread Starter Forum Replies Last Post
Identity Column mike_remember SQL Server 2000 3 July 9th, 2007 07:12 PM
checking column is either identity or not. g_vamsi_krish SQL Server 2000 2 March 17th, 2006 12:38 PM
getting identity column from the table g_vamsi_krish SQL Server 2000 1 March 15th, 2006 05:05 PM
identity column rohit_sant SQL Language 4 June 6th, 2005 08:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.