SET IDENTITY_INSERT tblXXX ON

• AlanM-242948

  Valued Member

  Points: 71

  More actions

  September 22, 2006 at 12:09 pm

  #172768

  CREATE PROCEDURE cspAPP_CreateInvSnapShotTable

   

    @dt_Date Datetime

  AS

  declare  @s_SourceTableName varchar(255),

   @s_Sql nvarchar(4000)

   

  set  @s_SourceTableName = 'tblInvSnapShot'

  /*

  set @s_Sql =N'If exists (Select 1 from sysobjects where name =''' + @s_SourceTableName + '_PhysInv'+ '_' + Convert(Char(8),@dt_Date,112) + ''') drop table ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ''

  exec(@s_Sql)

  */

  set @s_Sql =N'Create table ' + @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112) + '(''

   [PKID] int identity (1,1) NOT NULL,

   [FormTypeID] int NULL,

   [HeatNumID] int NULL,

   [CategoryID] int NOT NULL,

   [ItemCount] char(10) NULL,

   [Cost] money NULL,

   [SkidNum] char(12) NULL,

   [PONum] int NULL,

   [ItemNum] int NULL,

   [TotalWt] float NULL,

   [SnapShotDate] datetime NOT NULL,

   [TotalCost] money NULL,

   [GradeID] int NULL,

   [Width] float NULL,

   [Length] float NULL,

   [ItemNotes] char(800) NULL,

   [GaugeLow] float NULL,

   [GaugeHigh] float NULL,

   [OD] float NULL,

   [ODLow] float NULL,

   [ODHigh] float NULL,

   [Wall] float NULL,

   [Location] nvarchar(25) NULL,

   CONSTRAINT [PK_tblsnapShotl] PRIMARY KEY  CLUSTERED ([PKID])  ON [PRIMARY])'

  select @s_Sql

  exec(@s_Sql)

     /* SET IDENTITY_INSERT tblInvSnapShot_PhysInv ON   */

  set @s_Sql = N'SET IDENTITY_INSERT ' +  @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112)  + ' ON'

  select @s_Sql

  exec (@s_Sql)

  /*sql to insert data into the new tblInvSnapShot_PhysInv */

  set @s_Sql = N'INSERT INTO  ' +  @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112)  + '

  (PKID, FormTypeID, HeatNumID, CategoryID, ItemCount, Cost, SkidNum, PONum, ItemNum, TotalWt, SnapShotDate,

                        TotalCost, GradeID, Gauge, Width, Length, ItemNotes, GaugeLow, GaugeHigh, OD, ODLow, ODHigh, Wall, Location)

  SELECT     PKID, FormTypeID, HeatNumID, CategoryID, ItemCount, Cost, SkidNum, PONum, ItemNum, TotalWt, GETDATE() AS SnapShotDate,

                        Cost * TotalWt AS TotalCost, GradeID, Gauge, Width, Length, ItemNotes, GaugeLow, GaugeHigh, OD, ODLow, ODHigh, Wall, Location

  FROM         dbo.INVITEMS'

  select @s_Sql

  exec (@s_Sql)

  GO

  I get the error message "Cannot insert explicit value for identity column in table 'tblInvSnapShot_PhysInv_20060920' when IDENTITY_INSERT is set to OFF.".

  when I run  this in the query analyzer (code from above)

  set @s_Sql = N'SET IDENTITY_INSERT ' +  @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112)  + ' ON'

  select @s_Sql

  exec (@s_Sql)

  I get the correct SQL and it runs with no errors.

  Does the created table get immediately added to the system catalog? If not is this why the error message?

  Thanks,  Alan Monteath 

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 22, 2006 at 12:40 pm

  #662027

  The problem is the nature af dynamic sql.  You need to add the set identity insert on in the same dynamic sql that does the insert:

  CREATE PROCEDURE cspAPP_CreateInvSnapShotTable

    @dt_Date Datetime

  AS

  declare  @s_SourceTableName varchar(255),

   @s_Sql nvarchar(4000)

   

  set  @s_SourceTableName = 'tblInvSnapShot'

  /*

  set @s_Sql =N'If exists (Select 1 from sysobjects where name =''' + @s_SourceTableName + '_PhysInv'+ '_' + Convert(Char(8),@dt_Date,112) + ''') drop table ' + @s_SourceTableName + '_' + Convert(Char(8),@dt_Date,112) + ''

  exec(@s_Sql)

  */

  set @s_Sql =N'Create table ' + @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112) + '(''

   [PKID] int identity (1,1) NOT NULL,

   [FormTypeID] int NULL,

   [HeatNumID] int NULL,

   [CategoryID] int NOT NULL,

   [ItemCount] char(10) NULL,

   [Cost] money NULL,

   [SkidNum] char(12) NULL,

   [PONum] int NULL,

   [ItemNum] int NULL,

   [TotalWt] float NULL,

   [SnapShotDate] datetime NOT NULL,

   [TotalCost] money NULL,

   [GradeID] int NULL,

   [Width] float NULL,

   [Length] float NULL,

   [ItemNotes] char(800) NULL,

   [GaugeLow] float NULL,

   [GaugeHigh] float NULL,

   [OD] float NULL,

   [ODLow] float NULL,

   [ODHigh] float NULL,

   [Wall] float NULL,

   [Location] nvarchar(25) NULL,

   CONSTRAINT [PK_tblsnapShotl] PRIMARY KEY  CLUSTERED ([PKID])  ON [PRIMARY])'

  select @s_Sql

  exec(@s_Sql)

     /* SET IDENTITY_INSERT tblInvSnapShot_PhysInv ON   */

  set @s_Sql = N'SET IDENTITY_INSERT ' +  @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112)  + ' ON'

  select @s_Sql

  exec (@s_Sql)

  /*sql to insert data into the new tblInvSnapShot_PhysInv */

  set @s_Sql = @s_Sql + N';' + N'INSERT INTO  ' +  @s_SourceTableName + '_PhysInv' + '_' + Convert(Char(8),@dt_Date,112)  + '

  (PKID, FormTypeID, HeatNumID, CategoryID, ItemCount, Cost, SkidNum, PONum, ItemNum, TotalWt, SnapShotDate,

                        TotalCost, GradeID, Gauge, Width, Length, ItemNotes, GaugeLow, GaugeHigh, OD, ODLow, ODHigh, Wall, Location)

  SELECT     PKID, FormTypeID, HeatNumID, CategoryID, ItemCount, Cost, SkidNum, PONum, ItemNum, TotalWt, GETDATE() AS SnapShotDate,

                        Cost * TotalWt AS TotalCost, GradeID, Gauge, Width, Length, ItemNotes, GaugeLow, GaugeHigh, OD, ODLow, ODHigh, Wall, Location

  FROM         dbo.INVITEMS'

  select @s_Sql

  exec (@s_Sql)

  GO