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
milani_lucieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
0
milani_lucieAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
Yes, SET IDENTITY_INSERT should work with temp tables as well.

Ooh, just remembered, I think when you want to insert identity values you *must* specify a column list; I think SQL requires it.

So please try this:

insert into #TempTable (PK CustomerName) VALUES (3, 'AAAAA')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Thanks
0
milani_lucieAuthor Commented:
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
0
milani_lucieAuthor Commented:
Getting error with "Table Variable": Incorrect syntax near '@TableVar'

Just want to make sure that it will NOT work with "Table Variable" - Thanks
0
milani_lucieAuthor Commented:
Error here: set identity_insert @TableVar on

Thanks
0
wdosanjosCommented:
INSERT into an identity column not allowed on table variables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
milani_lucieAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.