Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2475
  • Last Modified:

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
0
milani_lucie
Asked:
milani_lucie
  • 6
  • 2
2 Solutions
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now