October 26, 2006 at 12:13 pm
I am trying to alter a table to include an IDENTITY column, but I need to use a variable to specify the seed value instead of the default 1. I get an error (Incorrect syntax near @idStart) on my ALTER TABLE statement.
DECLARE @idStart int (then code to set the @idStart value...)
ALTER TABLE UsersImport ADD userIdInt int IDENTITY (@idStart,1)
I don't get an error if I replace the variable @idStart with a 1. Anybody know what is wrong, or have suggestion how to do it better?
October 26, 2006 at 12:23 pm
normall, you just do ALTER TABLE UsersImport ADD userIdInt int IDENTITY (99,1) to start at #99.
with a variable, it's a two step process:
declare @C1 int
set @c1=99
ALTER TABLE UsersImport ADD userIdInt int IDENTITY (1,1)
DBCC CHECKIDENT( [UsersImport],RESEED,@c1)
Lowell
October 26, 2006 at 1:10 pm
The DBCC CHECKIDENT call doesn't seem to do anything, the identity values are the same after the call (start with 1). I get this message:
"Checking identity information: current identity value '1058', current column value '55'. DBCC execution completed. If DBCC printed error messages, contact your system administrator." (there are 1058 rows in my table).
Any ideas?
DECLARE @idStart int
set @idStart=55
ALTER TABLE UsersImport ADD userIdInt int IDENTITY (1,1)
DBCC CHECKIDENT(UsersImport, RESEED, @idStart)
October 26, 2006 at 1:11 pm
or direcly
ALTER TABLE UsersImport ADD userIdInt int IDENTITY (99,1)
or build the alter dymamic :
declare @stmt varchar(4000)
select @stmt="ALTER TABLE UsersImport ADD userIdInt int IDENTITY (" + convert(varchar(15),@yourvar) + ',1)"
exec (@stmt)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2006 at 1:14 pm
If you want the identities to start using your specific startvalue, you should specify it when adding the identity-column. Not afterward with a checkident !
Check books online !
regarding the reseed, check out bol for "DBCC CHECKIDENT" it explains it well !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2006 at 1:18 pm
the identity function is smart....if there is a max value in your db with the value 1058, if you try to reseed with a value LESS than the current max amount, it won't let you...so if you tried to set it to 55, it ignored the command because it knows that eventually, it would increment and find the 1058 value and raise an error.
It sounds like what you really want to do is insert into the table at #55 for a while.
there is no need to do that; the identity field is so that you don't have duplicates; if you want numbers in a consecutive order, even if you delete stuff, you should handle it differently.
Lowell
October 26, 2006 at 1:27 pm
I just need the rows numbered sequentially, starting with a variable number. I got it to work by creating the ALTER TABLE statement dynamically, as suggested above.
Thanks for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply