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

Goran Djuranovic wrote:
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 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



Show quoteHide quote

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.

  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



Show quoteHide quote

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 ;

[Reposted, as posts from outside msnews.microsoft.com does not seem to make
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.

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






Show quoteHide quote

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

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


Show quoteHide quote

Goran Djuranovic wrote:
Show quoteHide quoteOne 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).

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.developersdex.com ***

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://milambda.blogspot.com/

Rajiv Sharma (sharma_raj***@yahoo.co.uk) 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.

Rajiv,
You have to put your INSERT INTO code inside DynamicSQL for it to work.
This worked for me:
******************
Set @vcIdentityInsert = 'set IDENTITY_INSERT ' + @TargetDB +
'.dbo.tbl_File_Received ON ' +
'insert into tbl_File_Received values (blah,blah)'
Execute ( @vcIdentityInsert )

Set @vcIdentityInsert = 'set IDENTITY_INSERT ' + @TargetDB +
'.dbo.tbl_File_Received OFF'
Execute ( @vcIdentityInsert )
******************

Hope this helps
Goran Djuranovic


Show quoteHide quote

Deadlock help on single table
Cascading stock values?
Greater than zero
smalldatetime or integer
Trouble in using bcp to get data into table for MS SQL 2005