July 21, 2003 at 4:02 pm
Is it possible to set the Identity Seed and Identity Increment in T-SQL? I'm in a converion phase and I would like to use
T-SQL rather than T-SQL and EM to set the Identity Seed and Identity Increment after I load a table. The field is the primary index and it is an INT.
Can I do something like
"select max(id) from tableX" and then use the result in the T-SQL code to set the Identity Seed? The Identity Increment will be a fixed number of 1.
What I have been doing is running some SQL like "select max(id) from tableX" and then apply the changes using EM.
Thanks in advance for your time.
July 21, 2003 at 5:42 pm
How about something like this as an example?
declare @hival int
select @hival = max(id) from mytable
dbcc checkident(mytable, reseed, @hival)
HTH,
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
July 22, 2003 at 12:08 am
Hi sidomenico,
quote:
Is it possible to set the Identity Seed and Identity Increment in T-SQL? I'm in a converion phase and I would like to useT-SQL rather than T-SQL and EM to set the Identity Seed and Identity Increment after I load a table. The field is the primary index and it is an INT.
what about adding this field after you've loaded the table?
SQL Server will produce a fine consecutive numbering on it.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 22, 2003 at 9:42 am
July 22, 2003 at 10:34 am
Yes. DBCC CHECKIDENT with RESEED PARAMETER.
As in:
DBCC CHECKIDENT ('MyTable',RESEED, 1)
G. Milner
July 28, 2003 at 11:24 am
Thank you.
July 29, 2003 at 2:39 pm
The table I'm working with is created with an Identity Seed of 39999999. Then it is loaded using source data from a Mainframe Application which has a unique 9 digit number. I'm doing the following in a DTS Package:
1) SET IDENTITY_INSERT mytable ON
2) Load a .txt file to the table. Each record in the .txt file contains a unique number and is used as the id.
3) SET IDENTITY_INSERT mytable OFF
4) DECLARE @iIdentitySeed INT
SELECT @iIdentitySeed = MAX(id) FROM
mytable
DBCC CHECKIDENT(mytable,
reseed, @iIdentitySeed)
Everything is working fine. When a
new record is inserted into mytable via an ASP application, the id number created is the max(id) + 1.
My consern is that in EM the Identity Seed is still 39999999 where I thought it would be the max(id), which is 100227919.
Did the Identity Seed get "reseeded" or is SQL Server using the max id in the table and overriding the Identity Seed of 39999999.
I see that as records are added to a table the Identity Seed does not change. I thought it would when the "RESEED" was used.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply