Temporary Tables and Ident Fields
-
Can I create a temporary table (not using dynamic SQL - due to visibility outside of execution of SQL) where I can set the ident seed to a parameter driven value?
-
No, not directly - identity doesn't take a variable for it's seed.
This doesn't work...
declare @i int
set @i = 100
create table #x( id int not null identity(@i,1), myVal char(1) not null)
However, there is a workaround.
This actually works..
declare @i int
set @i = 100
create table #x( id int not null identity(1,1), myVal char(1) not null)
set identity_insert #x on
insert #x (id, myVal) select @i, 'X'
set identity_insert #x off
insert #x (myVal) select 'a'
select * from #x
drop table #x
..would that fix your problem? The net effect (though a bit convoluted) would be that you can use a variable to start where new identities will be generated. Note though, that if you truncate the table, the identity_insert on/off process has to be repeated, since truncate always resets the identity.
/Kenneth
-
thanks but I have a insert from a select statement.... what can I do in this case?
-
SELECT IDENTITY(INT,1,1) AS SNO, Name,address into #tmp from <table name>
select * from #tmp
-
Geoff,
You could you use Kenneth's idea with a slight modification. Assuming the starting ID is greater than 1 (you could check for this), use the following code:
declare @i int
set @i = 100 -- desired starting ID
create table #x
(
id int not null identity(1,1)
, myVal char(1) not null
)
set identity_insert #x on
insert #x (id, myVal) select @i - 1, 'X'
set identity_insert #x off
INSERT #x (myVal) select 'a' -- replace with your insert.
DELETE #x WHERE id = @i - 1 -- remove the initial row
select * from #x
drop table #x
---------------------------------------------------------------------
Alternatively, you could do this:
declare @i int
set @i = 100 -- desired starting ID
create table #x
(
id int not null identity(1,1)
, myVal char(1) not null
)
DBCC CHECKIDENT (#x, RESEED, @i) WITH NO_INFOMSGS
insert #x (myVal) select 'a' -- replace with your INSERT statement
select * from #x
drop table #x
-
thanks the reseed is a v.good idea!
-
Let me warn you that dbcc checkident requires elevated priviliges
* Noel -
Noel is correct, but permission to run DBCC CHECKIDENT extends to the table owner, which should be the user who creates the temp table.
-
Sorry but I differ:
FROM BOL:
Permissions
DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.
* Noel -
Noel is once again correct.
DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.
However, the owner of a temp table (in tempdb) is dbo, so unless the user is a member of the groups listed above, the DBCC CHECKIDENT method won't work. (I never use it myself).
-
I don't quite see why that should be a problem... Could you elaborate on your particular situation?
/Kenneth
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply