Resetting the Identity field

I have a composite pk in a table 'table' in ms sql server. value in one field 'table.a' is fk to another table 'table1.a'
value in field table.b is a id field. i need to reset this field 'table.b' to 1 each time the 'table.a' changes.

Any suggestions.Not sure what you mean.
if table.a is part of the primary key it should never change otherwise it shouldn't be part of the primary key.
It sounds like you might want a trigger but maybe you could post an example.|||i see what you mean. I have changed it and i have a field table1.a and table1.b. both .a and .b are not in the keys, though .a is a fk to table2.a.
i need to increment .b by 1 on each input of .a where .a = 'x' (say). as soon as .a = 'y' (say) i need to reset .b to 0 and auto increment as new values for .a='y' are inserted.
hope this makes sense.
thanks|||ok
you have table1(a,b)
a is an id and you want b to be the sequence number within a?

put a trigger on the table

create trigger tr_table1_ins on table1 for insert
as
set rowcount 1
while exists(select * from table1 where b is null)
begin
update table1
set b = (select max(b)+1 from table1 t1 where table1.a = t1.a)
where b is null
set rowcount 0
go

if you only ever insert one row at a time then you can just do the update without the loop.

Another option is to put the current value for b on table2 and increment it within a transaction on inserts and use it with the insert.|||We've done something like this at our site. When we needed to know the occurence of a record, example "2 of 5". We implemented a TRIGGER like nigelrivett has suggested. To use a trigger you should JOIN with the INSERTED table to update only those records that were Inserted.

SET NOCOUNT ON
GO
CREATE
TABLE Occurrence
(
syID int IDENTITY (1, 1) NOT NULL ,
colA char(3),
colB int NOT NULL DEFAULT 0
)
GO
CREATE
TRIGGER tri_Occurrence
ON Occurrence
FOR Insert
AS

--
-- If no records were effected then return
--
IF (@.@.ROWCOUNT = 0) BEGIN
RETURN
END

UPDATE o
SET colB = (SELECT MAX(o.colB) + 1 FROM Occurrence o WHERE i.colA = o.colA)
FROM Occurrence o,
Inserted i
WHERE o.syID = i.syID

RETURN
GO

INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('B')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('C')
INSERT Occurrence (colA) values ('C')
GO

SELECT *
FROM Occurrence

syID colA colB
---- -- ----
1 A 1
2 A 2
3 B 1
4 A 3
5 C 1
6 C 2|||Unfortunately that only works for single row inserts.
And assumes an ID on the table.
Apart from that is the same as my trigger.|||You are one to get the last word in. I'm sorry that I replied to the posting with my answer. I felt that a person could cut and paste this and see a working example.

But I forget that once nigelrivett answers, we should lock the posting, case closed.|||Sorry - just thought I'd point out a problem, which is quite common, with the trigger you posted.

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

resetting Identity Seed on change of primary key

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

Quote:

Originally Posted by Rick Kay

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.|||

Quote:

Originally Posted by folderol

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.

Tom, that's exactly what I thought, but I wanted to be sure someone else agreed with me. Thanks for your response.|||This will reseed the identity no for a column in a table.

declare @.intCounter int
set @.intCounter = 0
update (YOUR_TABLE)
SET @.intCounter = (YOUR_COLUMN) = @.intCounter + 1

resetting identity columns

Is there a way besides truncate table to reset the identity column of a tabl
e?
Thanks,
JoeLook at DBCC CHECKIDENT in Books Online.
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Jaylou,
Yes. DBCC CHECKIDENT ...RESEED argument.
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Thank you!
I knew there was something but I couldn't find it.|||Thank you!
I knew there was something but I couldn't find it.

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
Ansari
Check out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.
gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that..
.
> one method is to truncate table... but if table is used as parent in forei
gn
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Seed

O.k. here's my deal. I have a table who's items get assigned an ID based on
the identity seed. The identity seed is incremented by 1, and the seed
length is 4 . When my program is run, some of these items get moved into
another table for future use and the others are deleted. The items that get
moved are used again the next time the program is run(which is only once a
day). The one items get deleted is based on whether or not they match off t
o
another item read in by the program. Ex. If the data in my table is 1 2 3
4
5 and the program reads in 1 2 4 5, then the 3 is moved to another table and
the 1 2 4 5 get deleted b/c they were matched off. The identity seed is the
n
reset using DBCC CHECKIDENT which works fine, but I need to be able to have
the value it is reset to tied in to something like the date so that it is
unique each time it is reset. This way the next time my program reads in a
3, I need to make sure it does get the same identity seed as the previous 3
that was saved from the first time. I hope you can understand all that and
give me some insight as to how/if this can be done. Thanks.Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"CD" wrote:

> O.k. here's my deal. I have a table who's items get assigned an ID based
on
> the identity seed. The identity seed is incremented by 1, and the seed
> length is 4 . When my program is run, some of these items get moved into
> another table for future use and the others are deleted. The items that g
et
> moved are used again the next time the program is run(which is only once a
> day). The one items get deleted is based on whether or not they match off
to
> another item read in by the program. Ex. If the data in my table is 1 2
3 4
> 5 and the program reads in 1 2 4 5, then the 3 is moved to another table a
nd
> the 1 2 4 5 get deleted b/c they were matched off. The identity seed is t
hen
> reset using DBCC CHECKIDENT which works fine, but I need to be able to hav
e
> the value it is reset to tied in to something like the date so that it is
> unique each time it is reset. This way the next time my program reads in
a
> 3, I need to make sure it does get the same identity seed as the previous
3
> that was saved from the first time. I hope you can understand all that an
d
> give me some insight as to how/if this can be done. Thanks.|||Here's how I read your *requirement* as opposed to what you are
actually asking for. It seems like you have a table and you need to
determine which is the 1st, 2nd, 3rd or Nth row inserted to that table
on any particular day. So add a DATETIME column to the table:
CREATE TABLE YourTable (creation_date DATETIME NOT NULL UNIQUE DEFAULT
CURRENT_TIMESTAMP, ...)
The derive the sequence number like this:
SELECT T1.creation_date, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T2.creation_date >= '20050225'
AND T1.creation_date < '20050226'
AND T1.creation_date >= T2.creation_date
GROUP BY T1.creation_date
ORDER BY T1.creation_date
Resetting the seed and relying on IDENTITY to do the same thing is a
really bad idea. IDENTITY sequences can have gaps.
If I've completely misunderstood then the standard advice applies:
Please post DDL, sample data, required results to maximize your chance
of getting a good answer. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--sql

Reseting Identity Seed

hi all..
I want to reset the identity seed value for a table... How can I do that...
one method is to truncate table... but if table is used as parent in foreign
key relationship; it does not allow to truncate the table... any other
method?
AnsariCheck out DBCC CHECKIDENT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message news:uf4BRq4AFHA.4028@.TK2MSFTNGP15.phx.gbl...
> hi all..
>
> I want to reset the identity seed value for a table... How can I do that...
> one method is to truncate table... but if table is used as parent in foreign
> key relationship; it does not allow to truncate the table... any other
> method?
> Ansari
>
>

Reseting Identity Column using SQL

Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)

Thanks, radco

Take a look at the following pointer:http://msdn2.microsoft.com/en-US/library/ms176057.aspx

Reset the Identity Increment

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.

Ryan KennedyCheck out the DBCC CHECKIDENT command in google^h^h^h^h^h^h Books Online|||Also look at the TRUNCATE statement.

"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:53dPb.1926$kH2.252@.nwrdny01.gnilink.net...
> Reset the Identity Increment

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.

Reset Primary ID back to 1

Reset the Identity Increment
------------------------

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.The only way to do that is to use truncate table instead of delete.
You need additional rights to be able to execute truncate table statement.

Good Luck.

Irina.|||Yes, of course you can use truncate. It will delete and reseed the identity columns.
It is also more efficient way to save the resources.

But, if you also want to use delete, you can reset the identity column by running the following command:

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Hope to help.

Reset Next Range Starting Value?

Hello,
Is there any way to reset the "Next range starting value" on a table
with Identity Range management?
For some reason, the next range starting value has gone too high. The
maximum identity is 32767, and the next range starting value is 32650.
I did have the range size at subscribers set to 200, but there wasn't
enough room to give that range so the initial replication failed. I
changed it to 50 in order to get the initial subscription pulling.
The actual maximum identity in the table is less than 2000. I only
have 2 subscribers.
I'm not sure how it got so high, but is there a way to reset it?
Thanks,
Jeff
You can manually update the msrepl_identity (sp?) table in the distribution
database.
However, you should poll your subscribers and publisher to find out what is
in effect there.
Check the check constraint on the identity key and do a DBCC
checkindent('tablename') to get the current value and the value in use.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeff Hedlund" <jhedlund@.gmail.com> wrote in message
news:1116082608.807565.267540@.o13g2000cwo.googlegr oups.com...
> Hello,
> Is there any way to reset the "Next range starting value" on a table
> with Identity Range management?
> For some reason, the next range starting value has gone too high. The
> maximum identity is 32767, and the next range starting value is 32650.
> I did have the range size at subscribers set to 200, but there wasn't
> enough room to give that range so the initial replication failed. I
> changed it to 50 in order to get the initial subscription pulling.
> The actual maximum identity in the table is less than 2000. I only
> have 2 subscribers.
> I'm not sure how it got so high, but is there a way to reset it?
> Thanks,
> Jeff
>

reset next identity value

For a set of tables many of which contain FK constraints related to identity
cols in the foreign tables:
When deleting all data (dropping FK constraints first) and then repopulating
data from scripts , how to instruct SS to reset the next identity value to
the identity seed ?TRUNCATE TABLE, or check out DBCC CHECKIDENT (TableName, RESEED)
Roy
On Mon, 27 Feb 2006 17:14:18 -0800, "John A Grandy"
<johnagrandy-at-yahoo-dot-com> wrote:

>For a set of tables many of which contain FK constraints related to identit
y
>cols in the foreign tables:
>When deleting all data (dropping FK constraints first) and then repopulatin
g
>data from scripts , how to instruct SS to reset the next identity value to
>the identity seed ?

Reset Identity Seed to 0

How can I Reset Identity Seed to 0
thnaksTRUNCATE TABLE tablename
Also see DBCC CHECKIDENT in Books Online.
"Music Lover" <music@.my-heart.org> wrote in message
news:uvWV4TiXDHA.2384@.TK2MSFTNGP10.phx.gbl...
> How can I Reset Identity Seed to 0
> thnaks
>
>

Reset IDENTITY seed

Hello,
Can I reset the IDENTITY seed of a Table column without delete/drop the table?
I want to delete all the table rows, restore de seed, and restore abackup made on a XML (using SET IDENTITY_INSERT Table ON)
I cant drop the table due to acount restricctions.
regards,
Edu
You could try
TRUNCATE TABLE MyTable|||Check outDBCC CHECKIDENT.|||Thanks,
DBCC CHECKIDENT. works fine!
Regards,
Edu

Reset Identity Question

SQL Server 2000
Is there a way to reset an identity field of an empty field back to one
without createing a temp table and renaming?
TIA
Tim MorrisonDBCC CHECKIDENT (<tableNamehere>, RESEED, 1)
"Tim Morrison" wrote:

> SQL Server 2000
> Is there a way to reset an identity field of an empty field back to one
> without createing a temp table and renaming?
> TIA
> Tim Morrison
>
>|||On Wed, 9 Mar 2005 16:07:35 -0600, Tim Morrison wrote:

>SQL Server 2000
>Is there a way to reset an identity field of an empty field back to one
>without createing a temp table and renaming?
Hi Tim,
DBCC CHECKIDENT, with the RESEED option.
Another way would be to use TRUNCATE TABLE instead of DELETE FROM when
deleting the last row - TRUNCATE TABLE automatically resets the identity
seed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||For some tables this is what i do, but I have several parent-child tables,
and it appears I cannot do a TRUNCATE when there are child tables, even if
they are empty.
Tim Morrison
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qhvu21p88vbenjpop7e92h6pj0fubtrueq@.
4ax.com...
> On Wed, 9 Mar 2005 16:07:35 -0600, Tim Morrison wrote:
>
> Hi Tim,
> DBCC CHECKIDENT, with the RESEED option.
> Another way would be to use TRUNCATE TABLE instead of DELETE FROM when
> deleting the last row - TRUNCATE TABLE automatically resets the identity
> seed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Cool... seems to work... EXCEPT the next record that is inserted has a value
of 2 instead of 1. Its no big deal, I can deal with 2.
Tim Morrison
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:5FF180CC-BFB3-40FE-B2AC-2FE0F5F5291D@.microsoft.com...
> DBCC CHECKIDENT (<tableNamehere>, RESEED, 1)
>
> "Tim Morrison" wrote:
>|||reseed to 0 instead if you want 1
Simon Worth
"Tim Morrison" <sales_nospam_@.kjmsoftware.com> wrote in message
news:eeK316PJFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Cool... seems to work... EXCEPT the next record that is inserted has a
> value of 2 instead of 1. Its no big deal, I can deal with 2.
> Tim Morrison
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:5FF180CC-BFB3-40FE-B2AC-2FE0F5F5291D@.microsoft.com...
>

Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za
You can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>
|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za
|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>