?
Solved

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

Posted on 2012-04-09
9
Medium Priority
?
2,197 Views
Last Modified: 2012-04-09
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
Comment
Question by:milani_lucie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37823865
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
 

Author Comment

by:milani_lucie
ID: 37823876
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 37823910
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:milani_lucie
ID: 37823926
Great !! It works fine with "Temporary Table". Does it works with "Table Variable" ?

Thanks
0
 

Author Comment

by:milani_lucie
ID: 37823943
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
 

Author Comment

by:milani_lucie
ID: 37824035
Getting error with "Table Variable": Incorrect syntax near '@TableVar'

Just want to make sure that it will NOT work with "Table Variable" - Thanks
0
 

Author Comment

by:milani_lucie
ID: 37824041
Error here: set identity_insert @TableVar on

Thanks
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 1000 total points
ID: 37824114
INSERT into an identity column not allowed on table variables.
0
 

Author Comment

by:milani_lucie
ID: 37824165
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question