Link to home
Start Free TrialLog in
Avatar of milani_lucie
milani_lucieFlag for United States of America

asked on

Table Variable / Temporary Table (SET IDENTITY_INSERT ON) - SQL Server

Hi,

I have the following code. My first question is whether the SET IDENTITY_INSERT ON works with Table Variable / Temporary Table or not !!

CODE SEGMENT: 1

declare @TableVar table (PK int identity(1,1), CustomerName varchar(5))

insert Into @TableVar VALUES ('XXXXX')
insert Into @TableVar VALUES ('YYYYY')
insert Into @TableVar VALUES ('AAAAAA')  -- Error here
insert Into @TableVar VALUES ('ZZZZZ')

select * from @TableVar

set identity_insert on
insert into @TableVar VALUES (3, 'AAAAA')
set identity_insert off

select * from @TableVar

CODE SEGMENT: 2

create table #TempTable (PK int identity(1,1), CustomerName varchar(5))

insert Into #TempTable VALUES ('XXXXX')
insert Into #TempTable VALUES ('YYYYY')
insert Into #TempTable VALUES ('AAAAAA')  -- Error here
insert Into #TempTable VALUES ('ZZZZZ')

select * from #TempTable

set identity_insert on
insert into #TempTable VALUES (3, 'AAAAA')
set identity_insert off

select * from #TempTable

Thanks
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
insert Into @TableVar VALUES ('AAAAAA')  -- Error here

That's six bytes trying to go into a varchar(5), which probably accounts for an error there.
Avatar of milani_lucie
milani_lucie
Flag of United States of America image

ASKER

That is the reason i want to set the identity of value "3" (which was never used) using:

set identity_insert on
insert into @TableVar VALUES (3, 'AAAAA')
set identity_insert off

Thanks
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of milani_lucie
milani_lucie
Flag of United States of America image

ASKER

Great !! It works fine with "Temporary Table". Does it works with "Table Variable" ?

Thanks
Avatar of milani_lucie
milani_lucie
Flag of United States of America image

ASKER

Here is the complete code:

CODE SEGMENT: 1

declare @TableVar table (PK int identity(1,1), CustomerName varchar(5))

insert Into @TableVar VALUES ('XXXXX')
insert Into @TableVar VALUES ('YYYYY')
insert Into @TableVar VALUES ('AAAAAA')  -- Error here
insert Into @TableVar VALUES ('ZZZZZ')

select * from @TableVar

set identity_insert @TableVar on
insert into @TableVar (PK, CustomerName) VALUES (3, 'AAAAA')
set identity_insert @TableVar off

select * from @TableVar

CODE SEGMENT: 2

create table #TempTable (PK int identity(1,1), CustomerName varchar(5))

insert Into #TempTable VALUES ('XXXXX')
insert Into #TempTable VALUES ('YYYYY')
insert Into #TempTable VALUES ('AAAAAA')  -- Error here
insert Into #TempTable VALUES ('ZZZZZ')

select * from #TempTable

set identity_insert #TempTable on
insert into #TempTable (PK, CustomerName) VALUES (3, 'AAAAA')
set identity_insert #TempTable off

select * from #TempTable

Thanks
Avatar of milani_lucie
milani_lucie
Flag of United States of America image

ASKER

Getting error with "Table Variable": Incorrect syntax near '@TableVar'

Just want to make sure that it will NOT work with "Table Variable" - Thanks
Avatar of milani_lucie
milani_lucie
Flag of United States of America image

ASKER

Error here: set identity_insert @TableVar on

Thanks
ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of milani_lucie
milani_lucie
Flag of United States of America image

ASKER

Got the answer:

http://sqlwithmanoj.wordpress.com/tag/table-variables/

19. You cannot insert explicit values into an identity column (the table variable does not support the SET IDENTITY_INSERT ON).

Thanks