[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
0
DrLechter
Asked:
DrLechter
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
you should specify the column list here

set @sql = @sql + 'insert #xxx(x_id,i,j) values (1,2,3)'  
0
 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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