table with identity column issue.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

table with identity column issue.

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-07-29 : 04:50:26

hi all,
i have a table which has an identity column called Id.table sample is as follows.

id data
1 sqlserver
2 oacle
4 sybase

here in this table record with id=3 is deleted.so there is no id=3.
now can i insert a new record with id=3 in this table?
is it possible through a query?
thanks in advance.

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 05:16:56

Can. Must use SET IDENTITY_INSERT [database_name. [Schema_name]. ] Table (ON | OFF) before INSERT clause.

All "deleted ID "can get with the following request:
-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON
DECLARE @minidentval column_type
DECLARE @maxidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
FROM tablename
IF @minidentval = IDENT_SEED('tablename')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
FROM tablename t1
WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND
@maxidentval AND
NOT EXISTS (SELECT * FROM tablename t2
WHERE t2.$IDENTITY = t1.$IDENTITY +
IDENT_INCR('tablename'))
ELSE
SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN($IDENTITY) FROM img
IF @minidentval = IDENT_SEED('img')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
FROM img t1
WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
NOT EXISTS (SELECT * FROM img t2
WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
ELSE
SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-07-29 : 05:34:07

hi kpkirilov
thanks....
thanks for ur clarification

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 10:34:06

Is it really reqd to refill the gaps created in identity value due to deletion? You could leave it as it as and can use ROW_NUMBER() function to generate sequential number just in case you need it in queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources