April 17, 2008 at 9:23 am
Hi,
I'm trying to place a variable as a identity seed. Is this possible? I can't seem to find an answer anywhere... Below is my code:
declare @max1 int
select @max1 = max(acid)+1 from cms_2a
alter table cms_3a add cwid int identity(@max1, 1)
It says that I have an error on "@max1". If I can't place a variable as a seed, how else can I resolve this issue?
Thank you in advance!
April 17, 2008 at 9:47 am
You have an alter table in your post, so I have to ask, what are you trying to do? Are you trying to reseed the identity field? What about the data that currently exists in the table, or have you truncated the table?
😎
April 17, 2008 at 11:12 am
The whole reason to use a variable is if you are planning on doing this over and over and over again, each time with a potentially different value, as part of an automated process.
I'm curious as to why you would want to repeatedly add an Identity column to the same table, over and over, with different seed values each time. It seems kind of odd to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 11:25 am
Thank you for the replies!
Lynn Pettis - The reason I'm using an alter statement is because I wanted to add the "cwid" column with an identity seed starting at "max(acid)". There is data on the table "cms_3a" without an identity column so it's safe to say that the error doesn't occur due to multiple identity columns.
GSquared - It's true that this will be executed over and over again in SSIS for data conversions (the only things being changed would be data sources so the data will always differ during each execution). That is the reason I wanted to use a variable as the seed due to the unknown max ID in the column "acid".
I hope I've made this clearer!
Thank you so much!
April 17, 2008 at 12:20 pm
In that case, you might be better off using a Row_Number() function instead of an Identity.
Row_Number() over (whatever would be appropriate here) + Seed Value
Should give you the range you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 12:27 pm
dajonx (4/17/2008)
Thank you for the replies!Lynn Pettis - The reason I'm using an alter statement is because I wanted to add the "cwid" column with an identity seed starting at "max(acid)". There is data on the table "cms_3a" without an identity column so it's safe to say that the error doesn't occur due to multiple identity columns.
GSquared - It's true that this will be executed over and over again in SSIS for data conversions (the only things being changed would be data sources so the data will always differ during each execution). That is the reason I wanted to use a variable as the seed due to the unknown max ID in the column "acid".
I hope I've made this clearer!
Thank you so much!
If you were to simply create an identity field on the destination table, and insert into that table, the identity field would handle putting the right numbers next to the correct rows, each time, with no concerns about duplicates. In other words you seem to be trying to "reinvent the wheel" on some functionality that tends to cause you less trouble when you get out of its way and let it do its thing.
Do you REALLY need to control the identity building, and why? Just seems to me that you can do this a LOT easier.
(Just anticipating what your reason might be - have you ever heard of the OUTPUT clause of an INSERT command?)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 17, 2008 at 12:57 pm
Thank you for the suggestions!
GSquared - Thank you. I didn't know about the row_number() function and now I know a little more about it. However, I still cannot seem to seed the identity using this. Maybe I'm not thinking appropriately? My "acid" ID does start at 10000. I'm just trying to get "cwid" identity starting on the max ID of "acid". So basically, "cwid" the continuation of "acid" over multiple tables.
Matt Miller - Oh wow. I had no idea that there was such a thing as an OUTPUT clause as part of INSERT. I've read some help articles and I don't quite understand it still... Unfortunately, I do need to control the identity building ("cwid" on table cms_3a). As stated above, "cwid" is a continuation of "acid" over multiple tables which is why I'm trying to add an identity column on table "cms_3a" that is starting from the max value from "acid" + 1. They just have different column names.
I really am grateful for all of the help!! Thank you very much!
April 17, 2008 at 1:04 pm
All right. I just noticed your original code.
Try this....
declare @max1 int
select @max1 = max(acid)+1 from cms_2a
alter table cms_3a add cwid int identity(1, 1)
dbcc checkident(CMS_3a, RESEED,@max1)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 17, 2008 at 1:13 pm
GSquared - Thank you. I didn't know about the row_number() function and now I know a little more about it. However, I still cannot seem to seed the identity using this. Maybe I'm not thinking appropriately? My "acid" ID does start at 10000. I'm just trying to get "cwid" identity starting on the max ID of "acid". So basically, "cwid" the continuation of "acid" over multiple tables.
I was suggesting using Row_Number() instead of an identity value. Make the column be an identity (if you will continue to add rows after the initial insert), set identity_insert to "on", insert using Row_Number(), then turn off identity insert.
Re-seeding also works.
There are various possible solutions. What's best really depends on the end result you need. What will happen to the data after it is inserted into the table? What will it be used for? Will it be added to? Is it going to be used in OLTP or OLAP or some sort of hybrid? All of that matters to how you decide to go about using this ID column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 1:56 pm
Thank you!
Matt Miller - It doesn't seem that the "dbcc checkident(CMS_3a, RESEED, @max1)" is working. The IDs are still starting at 1, not 237526 (which is the current max ID from "acid"). The "acid" IDs is starting at 10000. Is that the problem? Below is the message I receive after executing the commands.
Checking identity information: current identity value '1156', current column value '237526'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
GSquared - The purpose of these columns is for assigning IDs to all of the data in the multiple tables. I figured that assigning identities would've been the easiest approach to accomplish this. It's basically a data dump and I'm assigning IDs to each record so I can then use those new IDs (for reference) as part of the data conversion in this company's schema. I won't be adding new data to it. By the way, the things you've suggested seems kind of complicated for a novice like me. :/
Thank you for the help!!
April 17, 2008 at 2:10 pm
you have to set the seeding BEFORE you insert stuff into the table. Seeding only affect NEW identities being set up. If the table was empty when you did that, and then you inserted AFTER the reseed - the ID's would be right....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 17, 2008 at 3:05 pm
The purpose of these columns is for assigning IDs to all of the data in the multiple tables. I figured that assigning identities would've been the easiest approach to accomplish this. It's basically a data dump and I'm assigning IDs to each record so I can then use those new IDs (for reference) as part of the data conversion in this company's schema. I won't be adding new data to it. By the way, the things you've suggested seems kind of complicated for a novice like me.
If you won't be adding data, don't bother with an identity column. That simplifies it tremendously.
Add the column, but don't make it an identity. Use Row_Number() when you insert the data. All done. Add the starting value, minus 1, to the Row_Number(), and it will start with that.
alter table cms_3a add cwid int
insert into cms_3a ((column list), cwid)
select (column list), Row_Number() over (order by SomeColumn) + (Seed Value-1)
from ...
I don't know what your other columns and table names are, or I could include them here, but that's the basic idea.
Sorry about it getting complicated. Sometimes, it's hard to know what's simple and what's complex, when it all seems so simple to me (but only because I've been doing this for years). I remember what it was like when I was new, and all of it was complex, but I sometimes don't remember the details.
Does that idea on row_number() help? It means you don't have to use identity, don't have to re-seed the identity, etc. You're just inserting a row number, which will be unique for each row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 6:52 am
Just checking, did you put the table name in single quotes in the DBBC CHECKIDENT command?
DBCC CHECKIDENT('tablename',RESEED,@value)
😎
April 18, 2008 at 7:40 am
I think GSquared's Row_Number() solution would be the way forward as you have control. An identity column may cause you issues down the line.
However, as you already have data in the target table and the Row_Number() function can only be used in the SELECT or ORDER BY clauses of a query, you can't readily update a column using it. If you have a unique key in your table, joining on a sub select of Row_Number() over that unique key will allow you to update the new column directly, alternatively, using SELECT INTO to a new table will also work:
SELECT
Row_Number() OVER (ORDER BY SomeColumn) + (@max1-1) AS cwid
, * --< replace with column list
INTO
new_table
FROM
original_table
If you must use an identity column, you could build the alter table statement dynamically (sorry guys!) like this:
DECLARE @max1 int
DECLARE @SQL nvarchar(1000)
SELECT @max1 = MAX(acid)+1 FROM cms_2a
SET @SQL = N'ALTER TABLE cms_3a ADD cwid int IDENTITY(' + STR(@max1) + ', 1)'
EXEC sp_executesql @SQL
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply