Setting identity_insert in dynamic sql

I am trying to set identity_insert on inside a dynamic sql statement.  For some reason, I keep getting the error "An explicit value for the identity column in table '#xxx' can only be specified when a column list is used and IDENTITY_INSERT is ON."  Since I am turning on identity_insert I should not be getting this error.  Help!

Note:  I must continue to use dynamic sql.  This code is an excerpt from a larger routine.


CREATE PROCEDURE dbo.spTestIdentityInsert
AS
declare @sql nvarchar (4000)
      
      create table #xxx
            (x_id int identity (1, 1))
      
      set @sql = 'set identity_insert #xxx on'  
      set @sql = @sql + ' '
      set @sql = @sql + 'insert #xxx values (1,2,3)'  

      exec sp_executesql @sql
GO
LVL 4
DrLechterAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
this is the complete code

ALTER PROCEDURE dbo.spTestIdentityInsert
AS
declare @sql nvarchar (4000)

     create table #xxx
          (x_id int identity (1, 1),i int, j int )
     
     set @sql = 'set identity_insert #xxx on '
     set @sql = @sql + char(13)
     set @sql = @sql + 'insert #xxx(x_id,i,j) values (1,2,3)'  

    exec sp_executesql @sql

GO


exec spTestIdentityInsert


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you should specify the column list here

set @sql = @sql + 'insert #xxx(x_id,i,j) values (1,2,3)'  
0
 
SireesCommented:
ALTER PROCEDURE dbo.spTestIdentityInsert
AS
declare @sql nvarchar (4000)

     create table #xxx
          (x_id int identity (1, 1),a int, b int )
     
     EXEC('SET IDENTITY_INSERT #xxx ON INSERT INTO #xxx (x_id,a,b) VALUES(1,2,3) SET IDENTITY_INSERT #xxx OFF')

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

All Courses

From novice to tech pro — start learning today.