|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Setting the Seed of an IDENTITY field on a temp table programaticaposting it here. I'm working on a stored procedure (running on SQL 2000) that creates a temp table in memory (saved as a variable in memory called @TempTable and defined as a TABLE), and that table has a column that is signified as an identity field (with seed of 1, and increment of 1). Now I need to add logic (in T-SQL) so I can do a query on data first, and set the seed value of that temp table. Unfortunately, I've had zero luck figuring that out. I tried setting a variable and including that in the CREATE TABLE (for the seed), and that didn't work. I even found (and tried) DBCC CHECKIDENT but it doesn't find the temp table. Any ideas/suggestions? For what it's worth, the whole reason I'm doing this, is I needed to have a column that auto increments before being inserted into a (real) database table. So my solution was to insert all my data into a temp table (with that column being an IDENTITY field on that table), and then an insert from that table into the real one. I thought it was working fine (it just would start counting at 1 every time), but this morning it was revealed that we need to have it start counting at the next value in the database (for given criteria). In this case, it needs to find the next highest number for that specific Project (one column in the database), and for that specific Unit (another column). In other words, every time the Project/Unit changes, it would need to start counting at 1 again (which is already the way the database is designed - in fact, the original design included a trigger that would set that column to the next highest value based on this criteria - the problem with my current stored procedure, is that it was happening too fast, and the trigger was setting all values to '1'). Can anyone help? Thanks! -Scott > For what it's worth, the whole reason I'm doing this, is I needed to have So you don't actually need to change the seed. You just need to make sure > a > column that auto increments before being inserted into a (real) database > table. So my solution was to insert all my data into a temp table (with > that > column being an IDENTITY field on that table), and then an insert from > that > table into the real one. that, during the insert into the actual table, you account for the maximum pre-existing value. Once you phrase the problem that way, you should see the solution. Hint - you just use a bit of very simple math (which you already need to do with your current design) on the identity column during the final insert. Just take the maximum value from your current table before creating your
temp table and use that value? Assuming i understand your question correctly? Immy Show quote "Scott Lyon" <scott.lyon.NOSPAM@NOSPAM.siemens.com> wrote in message news:9E895B93-5F4A-4BEF-8266-844176CA54CD@microsoft.com... >I have a quick question, but I've been unable to find the answer, so I'm > posting it here. > > > I'm working on a stored procedure (running on SQL 2000) that creates a > temp > table in memory (saved as a variable in memory called @TempTable and > defined > as a TABLE), and that table has a column that is signified as an identity > field (with seed of 1, and increment of 1). > > > Now I need to add logic (in T-SQL) so I can do a query on data first, and > set the seed value of that temp table. > > Unfortunately, I've had zero luck figuring that out. I tried setting a > variable and including that in the CREATE TABLE (for the seed), and that > didn't work. I even found (and tried) DBCC CHECKIDENT but it doesn't find > the > temp table. > > > Any ideas/suggestions? > > > > For what it's worth, the whole reason I'm doing this, is I needed to have > a > column that auto increments before being inserted into a (real) database > table. So my solution was to insert all my data into a temp table (with > that > column being an IDENTITY field on that table), and then an insert from > that > table into the real one. > > I thought it was working fine (it just would start counting at 1 every > time), but this morning it was revealed that we need to have it start > counting at the next value in the database (for given criteria). In this > case, it needs to find the next highest number for that specific Project > (one > column in the database), and for that specific Unit (another column). > > In other words, every time the Project/Unit changes, it would need to > start > counting at 1 again (which is already the way the database is designed - > in > fact, the original design included a trigger that would set that column to > the next highest value based on this criteria - the problem with my > current > stored procedure, is that it was happening too fast, and the trigger was > setting all values to '1'). > > > Can anyone help? > > Thanks! > -Scott That was what I tried first. I did my query to find the maximum value, stored
that in an Int variable called @MaxValue, and then tried to declare the table as: DECLARE @TempTable TABLE ( RowNumber int NOT NULL IDENTITY (@MaxValue, 1), OtherData Varchar(50) ) and it wouldn't take that. It didn't seem to like that one bit. Show quote "Immy" wrote: > Just take the maximum value from your current table before creating your > temp table and use that value? > Assuming i understand your question correctly? > Immy > "Scott Lyon" <scott.lyon.NOSPAM@NOSPAM.siemens.com> wrote in message > news:9E895B93-5F4A-4BEF-8266-844176CA54CD@microsoft.com... > >I have a quick question, but I've been unable to find the answer, so I'm > > posting it here. > > > > > > I'm working on a stored procedure (running on SQL 2000) that creates a > > temp > > table in memory (saved as a variable in memory called @TempTable and > > defined > > as a TABLE), and that table has a column that is signified as an identity > > field (with seed of 1, and increment of 1). > > > > > > Now I need to add logic (in T-SQL) so I can do a query on data first, and > > set the seed value of that temp table. > > > > Unfortunately, I've had zero luck figuring that out. I tried setting a > > variable and including that in the CREATE TABLE (for the seed), and that > > didn't work. I even found (and tried) DBCC CHECKIDENT but it doesn't find > > the > > temp table. > > > > > > Any ideas/suggestions? > > > > > > > > For what it's worth, the whole reason I'm doing this, is I needed to have > > a > > column that auto increments before being inserted into a (real) database > > table. So my solution was to insert all my data into a temp table (with > > that > > column being an IDENTITY field on that table), and then an insert from > > that > > table into the real one. > > > > I thought it was working fine (it just would start counting at 1 every > > time), but this morning it was revealed that we need to have it start > > counting at the next value in the database (for given criteria). In this > > case, it needs to find the next highest number for that specific Project > > (one > > column in the database), and for that specific Unit (another column). > > > > In other words, every time the Project/Unit changes, it would need to > > start > > counting at 1 again (which is already the way the database is designed - > > in > > fact, the original design included a trigger that would set that column to > > the next highest value based on this criteria - the problem with my > > current > > stored procedure, is that it was happening too fast, and the trigger was > > setting all values to '1'). > > > > > > Can anyone help? > > > > Thanks! > > -Scott > > > The IDENTITY constraint only accepts literal values.
Have you read the article at the link I've posted? ML --- http://milambda.blogspot.com/ Yes, and I've managed to apply some of what I learned from that article.
Looks like it's working (at least in my preliminary tests). Thanks for the help, everyone! Show quote "ML" wrote: > The IDENTITY constraint only accepts literal values. > > Have you read the article at the link I've posted? > > > ML > > --- > http://milambda.blogspot.com/ Maybe you should consider a simpler approach:
http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html ML --- http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics