Dynamic IDENTITY seed

Dynamic IDENTITY seed

Post by Goran Djur » Sun, 26 Mar 2006 01:10:45


his is a multi-part message in MIME format.


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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi all,</FONT></DIV>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>For example,</FONT></DIV>
<DIV><FONT face=Arial
size=2>----------------------------------------------------------------------</FONT></DIV>
<DIV><FONT face=Arial color=#000080 size=2>Declare @MySeed int</FONT></DIV>
<DIV><FONT face=Arial color=#000080 size=2>Select @MySeed = Max(SomeField) From
SomeTable</FONT></DIV>
<DIV><FONT face=Arial color=#000080 size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2><FONT color=#000080 size=2>Declare @MyVariableTbl
Table <BR>( <BR>UniqueID int Identity(@MySeed, 1) Primary Key Clustered Not
Null<BR>)</FONT></FONT></DIV>
<DIV><FONT face=Arial
size=2>---------------------------------------------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2>Error Msg:</FONT></DIV>
<DIV><FONT face=Arial size=2>Server: Msg 170, Level 15, State 1, Line 6<BR>Line
6: Incorrect syntax near <A href="mailto:'@MySeed'">'@MySeed'</A>.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>OR,</FONT></DIV>
<DIV><FONT face=Arial
size=2>---------------------------------------------------------------------<
 
 
 

Dynamic IDENTITY seed

Post by Robert Kle » Sun, 26 Mar 2006 01:28:28


What's your problem with using dynamic DDL?

Kind regards

robert

 
 
 

Dynamic IDENTITY seed

Post by Goran Djur » Sun, 26 Mar 2006 02:53:26

his is a multi-part message in MIME format.


Because I would have to write the whole SP as a dynamic SQL? I am using a bunch of table variables, XML pointers and some other stuff. It is just too complex 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" < XXXX@XXXXX.COM > wrote in message news:%23qep8$ XXXX@XXXXX.COM ...
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2>Because I would have to write the whole SP as a
dynamic SQL? I am using a bunch of table variables, XML pointers and some
other stuff. It is just too complex to be all in Dynamic SQL.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I found one way to do it by using temp
table:</FONT></DIV>
<DIV><FONT face=Arial
size=2>-------------------------------------------------------------------<BR><FONT
color=#000080>Create Table #MyTempTbl<BR>( <BR>UniqueID int Identity(1, 1)
Primary Key Clustered Not Null<BR>)</FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial color=#000080 size=2>Declare @MySeed int</FONT></DIV>
<DIV><FONT face=Arial color=#000080 size=2>Select @MySeed = Max(SomeField) From
SomeTable</FONT></DIV>
<DIV><FONT color=#000080></FONT> </DIV>
<DIV><FONT color=#000080>DBCC CHECKIDENT ('#MyTempTbl', RESEED,
@NewSeed)</FONT></DIV></FONT></DIV>
<DIV><FONT face=Arial
size=2>-------------------------------------------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2><STRONG>BUT, I would really like to use table
variable instead.</STRONG></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thanks for your response.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Goran Djuranovic</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>"Robert Klemme" <</FONT><A
href="mailto: XXXX@XXXXX.COM "><FONT face=Arial
size=2> XXXX@XXXXX.COM </FONT><
 
 
 

Dynamic IDENTITY seed

Post by oj » Sun, 26 Mar 2006 14:12:04

his is a multi-part message in MIME format.


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" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ...
Because I would have to write the whole SP as a dynamic SQL? I am using a bunch of table variables, XML pointers and some other stuff. It is just too complex 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" < XXXX@XXXXX.COM > wrote in message news:%23qep8$ XXXX@XXXXX.COM ...
> Goran Djuranovic wrote:
>> 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.
>
> What's your problem with using dynamic DDL?
>
> Kind regards
>
> robert
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2838" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><BR>-- <BR>-oj</DIV>
<DIV> </DIV>
<DIV><BR> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Goran Djuranovic" <<A
href="mailto: XXXX@XXXXX.COM "> XXXX@XXXXX.COM </A>>
wrote in message <A
href="news: XXXX@XXXXX.COM ">news: XXXX@XXXXX.COM </A>...</DIV>
<DIV><FONT face=Arial size=2>Because I would have to write the whole SP as a
dynamic SQL? I am using a bunch of table variables, XML pointers and some
other stuff. It is just too complex to be all in Dynamic SQL.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I found one way to do it by using temp
table:</FONT></DIV>
<DIV><FONT face=Arial
size=2>-------------------------------------------------------------------<BR><FONT
color=#000080>Create Table #MyTempTbl<BR>( <BR>UniqueID int Identity(1,
1) Primary Key Clustered Not Null<BR>)</FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial color=#000080 s
 
 
 

Dynamic IDENTITY seed

Post by David Port » Sun, 26 Mar 2006 17:42:52


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://www.yqcomputer.com/ (en-US,SQL.90).aspx
 
 
 

Dynamic IDENTITY seed

Post by Erland Som » Sun, 26 Mar 2006 19:35:55

Goran Djuranovic ( XXXX@XXXXX.COM ) 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, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

Dynamic IDENTITY seed

Post by --CELKO- » Tue, 28 Mar 2006 00:36:14

>> Because I would have to write the whole SP as a dynamic SQL? I am using a bunch of table variables, XML pointers and some other stuff. It is just too complex to be all in Dynamic SQL. <<

Perhaps if we knew your real problem, we could suggest a RDBMS
solution. You seem to go out of your way to avoid ANYTHING that looks
like SQL and RDBMS and/or it not proprietary extensions.

1) dynamic SQL tells us that you have no relational design and have to
fake it at run time
2) table variables say that you do not know what a table is supposed
to be and cannot write a query without faking a magnetic tape scratch
file
3) XML pointers? The word "pointers" is bad enough to RM people, but
we also view XML as a way to mve data, not to store or manipulate it
4) Using IDENTITY is completely non-relational, but using a sequence
that it not determined until run time is awful.
5) some other stuff. That has to be really awful.

With this kind of non-relational mindset, why are you using SQL at all?
This is what the other posters want to tell you, but they are too
polite to say it.
 
 
 

Dynamic IDENTITY seed

Post by Erland Som » Tue, 28 Mar 2006 05:25:48

[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]



Goran Djuranovic ( XXXX@XXXXX.COM ) 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, XXXX@XXXXX.COM Online for SQL
Server 2005
at http://www.yqcomputer.com/
Books Online for SQL Server 2000
at http://www.yqcomputer.com/
 
 
 

Dynamic IDENTITY seed

Post by Goran Djur » Wed, 29 Mar 2006 00:35:21

his is a multi-part message in MIME format.


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) and 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 ExternalTeamID and ExternalPlayerID values.
So in a nutshell, if records a successfully inserted into Player table, their 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 #PlayerMappingInsertTbl 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" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ...
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2>Hi Erland,</FONT></DIV>
<DIV><FONT face=Arial size=2>First, thanks for your response. Here is the
situation so far :-)  :</FONT></DIV>
<DIV><FONT face=Arial size=2>- I insert 5 records into a table (Player)
with identity column (PlayerID) and name, so for example, the
identities for those records get created as 3,4,5,6,7. </FONT></DIV>
<DIV><FONT face=Arial size=2>- I also have a mapping table
(PlayerMapping) with 3 fields (ExternalTeamID, ExternalPlayerID, PlayerID),
all INTs, no identity. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Now, what I need to do is the
following:</FONT></DIV>
<DIV><FONT face=Arial size=2
 
 
 

Dynamic IDENTITY seed

Post by David Port » Wed, 29 Mar 2006 01:45:03

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' ;

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://www.yqcomputer.com/ (en-US,SQL.90).aspx
 
 
 

Dynamic IDENTITY seed

Post by Goran Djur » Wed, 29 Mar 2006 04:43:06

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. :-)))))
 
 
 

Dynamic IDENTITY seed

Post by David Port » Wed, 29 Mar 2006 04:54:38


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).

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://www.yqcomputer.com/ (en-US,SQL.90).aspx
 
 
 

Dynamic IDENTITY seed

Post by Rajiv Shar » Fri, 28 Apr 2006 17:51:29

I'm trying to set IDENTITY_INSERT ON/OFF at runtime. But its failing.
Would appreciate a help. Following is the code snippet:

Set @vcIdentityInsert = 'set IDENTITY_INSERT ' + @TargetDB +
'.dbo.tbl_File_Received ON'
execute ( @vcIdentityInsert )
-- insert into tbl_File_Received
Set @vcIdentityInsert = 'set IDENTITY_INSERT ' + @TargetDB +
'.dbo.tbl_File_Received OFF'
execute ( @vcIdentityInsert )


*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

Dynamic IDENTITY seed

Post by TUw » Fri, 28 Apr 2006 18:51:02

Please explain what "it fails" means - are there errors? What is the error
message?

Also try printing the concatenated string before attempting to execute it.


ML

---
http://www.yqcomputer.com/
 
 
 

Dynamic IDENTITY seed

Post by Erland Som » Fri, 28 Apr 2006 20:26:34

Rajiv Sharma ( XXXX@XXXXX.COM ) writes:

The effeect ot a SET command lasts only to the end of the batch, so this
won't fly.

If you tell us of the real problem you are trying to solve, we might be
able to come up with some good ideas.

Generally, I am leaning towards the position that if you need to do
SET IDENTITY_INSERT as part of application code, you should probably not
use IDENTITY at all.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/