Dynamic IDENTITY seed

Hi all,
Does anyone know if it is possible to set identity seed using a variable, an
d if not what is the alternative? I would not like to use Dynamic SQL. I can
not get any of the following to work.
For example,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(@.MySeed, 1) Primary Key Clustered Not Null
)
---
Error Msg:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.MySeed'.
OR,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
DBCC CHECKIDENT (@.MyTableVariable, RESEED, @.NewSeed)
---
Error Msg:
Server: Msg 2501, Level 16, State 2, Line 13
Could not find a table or object named '@.MyVariableTbl'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
TIA
Goran DjuranovicGoran Djuranovic wrote:
> Hi all,
> Does anyone know if it is possible to set identity seed using a variable, and if n
ot what is the alternative? I would not like to use Dynamic SQL. I cannot get any of
the following to work.
What's your problem with using dynamic DDL?
Kind regards
robert|||Because I would have to write the whole SP as a dynamic SQL? I am using a bu
nch of table variables, XML pointers and some other stuff. It is just too co
mplex to be all in Dynamic SQL.
I found one way to do it by using temp table:
---
Create Table #MyTempTbl
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
DBCC CHECKIDENT ('#MyTempTbl', RESEED, @.NewSeed)
---
BUT, I would really like to use table variable instead.
Thanks for your response.
Goran Djuranovic
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:%23qep8$1TGHA.6048@.TK2MSFTNGP11.ph
x.gbl...
> Goran Djuranovic wrote:
>
> What's your problem with using dynamic DDL?
>
> Kind regards
>
> robert|||What you're asking for is not possible. You cannot reseed the ident for @.tb
and dynamic @.tb creation won't help either because the @.tb is bound to that
execution context.
--
-oj
"Goran Djuranovic" <goran.djuranovic@.newsgroups.nospam> wrote in message new
s:uko3bv2TGHA.5972@.TK2MSFTNGP10.phx.gbl...
Because I would have to write the whole SP as a dynamic SQL? I am using a bu
nch of table variables, XML pointers and some other stuff. It is just too co
mplex to be all in Dynamic SQL.
I found one way to do it by using temp table:
---
Create Table #MyTempTbl
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
DBCC CHECKIDENT ('#MyTempTbl', RESEED, @.NewSeed)
---
BUT, I would really like to use table variable instead.
Thanks for your response.
Goran Djuranovic
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:%23qep8$1TGHA.6048@.TK2MSFTNGP11.ph
x.gbl...
> Goran Djuranovic wrote:
>
> What's your problem with using dynamic DDL?
>
> Kind regards
>
> robert|||"Goran Djuranovic" <goran.djuranovic@.newsgroups.nospam> wrote in message
news:%237OGE21TGHA.4952@.TK2MSFTNGP09.phx.gbl...
Hi all,
Does anyone know if it is possible to set identity seed using a variable,
and if not what is the alternative? I would not like to use Dynamic SQL. I
cannot get any of the following to work.
For example,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(@.MySeed, 1) Primary Key Clustered Not Null
)
---
Error Msg:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.MySeed'.
OR,
---
Declare @.MySeed int
Select @.MySeed = Max(SomeField) From SomeTable
Declare @.MyVariableTbl Table
(
UniqueID int Identity(1, 1) Primary Key Clustered Not Null
)
DBCC CHECKIDENT (@.MyTableVariable, RESEED, @.NewSeed)
---
Error Msg:
Server: Msg 2501, Level 16, State 2, Line 13
Could not find a table or object named '@.MyVariableTbl'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
TIA
Goran Djuranovic
If your table was a permanent or temporary one rather than a table variable
then the following would have the same effect (almost). Although the seed
isn't changed directly the next value inserted will take on the value of
MAX(somecol)+increment. AFAIK you can't do this with a table variable. If
this doesn't help then maybe you could explain a bit more about what you are
trying to achieve.
SET IDENTITY_INSERT your_table ON ;
BEGIN TRAN ;
INSERT INTO your_table (uniqueid)
SELECT MAX(somecol)
FROM other_table ;
ROLLBACK TRAN ;
SET IDENTITY_INSERT your_table OFF ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Goran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
> Does anyone know if it is possible to set identity seed using a variable,
> and if not what is the alternative?
To suggest alternatives it would be very helpful to know what you are
really wahy you are trying to achieve. Setting the seed dynamically
sounds like a very odd request, so there is a good chance that the
solution to your real problem is entirely different.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
First, thanks for your response. Here is the situation so far :-) :
- I insert 5 records into a table (Player) with identity column (PlayerID) a
nd name, so for example, the identities for those records get created as 3,4
,5,6,7.
- I also have a mapping table (PlayerMapping) with 3 fields (ExternalTeamID,
ExternalPlayerID, PlayerID), all INTs, no identity.
Now, what I need to do is the following:
- After the first insert in Player, I also need to insert those identities (
3,4,5,6,7) into a PlayerMapping's PlayerID column. Don't worry about Externa
lTeamID and ExternalPlayerID values.
So in a nutshell, if records a successfully inserted into Player table, thei
r keys need to be mapped into PlayerMapping table.
Here is the code excerpt from the sproc:
****************************************
******************************
/** Assign last identity value from PlayerTest table. Used for reseeding #Pl
ayerMappingInsertTbl table. **/
If (Select Count(1) From Player) > 1
Begin
Set @.LastIdentityInt = Ident_Current('Player') + 1
End
Else
Begin
Set @.LastIdentityInt = Ident_Current('Player')
End
/** Insert Players that need to be inserted and are valid. **/
Insert Into Player (LeagueID, Name)
Select
LeagueID,
Name
From @.PlayerTbl PlayerTbl
Where IsValid = '1' And OperationToDo = 'Insert'
Order By RowID
/** Insert Players into mapping table. **/
Create Table #PlayerMappingInsertTbl
(
ExternalTeamID varchar (20) Not Null,
ExternalPlayerID varchar (20) Not Null,
PlayerID int Identity(0, 1) Primary Key Clustered Not Null
)
DBCC CheckIdent ('#PlayerMappingInsertTbl', ReSeed, @.LastIdentityInt)
Insert Into #PlayerMappingInsertTbl(ExternalTeamID, ExternalPlayerID)
Select
ExternalTeamID,
ExternalPlayerID
From @.PlayerTbl PlayerTbl
Where IsValid = '1' And OperationToDo = 'Insert'
Order By RowID
Insert Into PlayerMapping
Select
ExternalTeamID,
ExternalPlayerID,
PlayerID
From #PlayerMappingInsertTbl
Drop Table #PlayerMappingInsertTbl
****************************************
*********************************
Thanks again
Goran Djuranovic
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message news:Xns9792E42A55D38Yazorman@.1
27.0.0.1...
> [Reposted, as posts from outside msnews.microsoft.com does not seem to mak
e
> it in.]
>
>
>
> Goran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
>
> To suggest alternatives it would be very helpful to know what you are
> really wahy you are trying to achieve. Setting the seed dynamically
> sounds like a very odd request, so there is a good chance that the
> solution to your real problem is entirely different.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQ
L
> Server 2005
> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.ms
px
> Books Online for SQL Server 2000
> athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Please include DDL with future posts so that we don't have to guess your
table structures, keys and so forth.
Logically it looks like you don't need the temp table or the dynamic
IDENTITY value to do this. I'm guessing of course because I haven't seen
your table structures (did I mention how important it it to post DDL? :-).
Try:
/** Insert Players that need to be inserted and are valid. **/
INSERT INTO Player (leagueid, name)
SELECT DISTINCT leagueid, name
FROM @.PlayerTbl
WHERE IsValid = '1'
AND operationtodo = 'Insert' ;
INSERT INTO PlayerMapping (externalteamid, externalplayerid, playerid)
SELECT T.externalteamid, T.externalplayerid, P.playerid
FROM @.PlayerTbl T
JOIN Player AS P
ON T.name = P.name
AND T.leagueid = P.leagueid
WHERE isvalid = '1'
AND operationtodo = 'Insert' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||Hi David,
In terms of DDL, I was just testing your brains. :-))))))) And, you did fine
:-)))))))
The code you sent below is exactly what I need. I totally forgot about JOIN
to LeagueID (mostly because I didn't have it in my @.PlayerTbl, but I was
able to add it after your suggestion). Your suggestion is as clean and
elegant as Einstein's E=m*c2. :-)))))
Seriously, thank you very much for your suggestion.
Goran Djuranovic
P.S. Some SQL problems can be solved even without DDL. :-)))))
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eCdYt3bUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> Please include DDL with future posts so that we don't have to guess your
> table structures, keys and so forth.
> Logically it looks like you don't need the temp table or the dynamic
> IDENTITY value to do this. I'm guessing of course because I haven't seen
> your table structures (did I mention how important it it to post DDL? :-).
> Try:
> /** Insert Players that need to be inserted and are valid. **/
> INSERT INTO Player (leagueid, name)
> SELECT DISTINCT leagueid, name
> FROM @.PlayerTbl
> WHERE IsValid = '1'
> AND operationtodo = 'Insert' ;
> INSERT INTO PlayerMapping (externalteamid, externalplayerid, playerid)
> SELECT T.externalteamid, T.externalplayerid, P.playerid
> FROM @.PlayerTbl T
> JOIN Player AS P
> ON T.name = P.name
> AND T.leagueid = P.leagueid
> WHERE isvalid = '1'
> AND operationtodo = 'Insert' ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Goran Djuranovic wrote:
> Hi David,
> In terms of DDL, I was just testing your brains. :-))))))) And, you did fi
ne
> :-)))))))
> The code you sent below is exactly what I need. I totally forgot about JOI
N
> to LeagueID (mostly because I didn't have it in my @.PlayerTbl, but I was
> able to add it after your suggestion). Your suggestion is as clean and
> elegant as Einstein's E=m*c2. :-)))))
> Seriously, thank you very much for your suggestion.
> Goran Djuranovic
> P.S. Some SQL problems can be solved even without DDL. :-)))))
>
One thing I should have mentioned is that my solution may fail if
(leagueid, name) isn't unique in the Players table. Make sure you
declare a UNIQUE key on those two columns. That's an example of why
accurate DDL is important - if I'd known the keys I wouldn't have had
to make an assumption which may not be valid. (Although for reasons of
good design that key is pretty much a given anyway based on your source
code).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--