IDENTITY_INSERT

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  August 2, 2013 at 5:51 am

  #297156

  Hi, when I try this query it gives me an error

  An explicit value for the identity column in table 'DC1..DMAuditHistory' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  DECLARE @projectId int

  SELECT @projectId = ProjectId FROm Project with(nolocK) WHERE ProjectNo = '61N24643'

  SET IDENTITY_INSERT DC1..DMAuditHistory ON

  INSERT INTO DC1..DMAuditHistory SELECT * FROM DMAuditHistory with(nolock) WHERE ProjectId = @projectId

  SET IDENTITY_INSERT DMAuditHistory OFF

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  August 2, 2013 at 5:58 am

  #1638125

  I have solved myself_

  Changed the query to this--

  DECLARE @projectId int

  SELECT @projectId = ProjectId FROm Project with(nolocK) WHERE ProjectNo = '61N24643'

  SET IDENTITY_INSERT DC1..DMAuditHistory ON

  INSERT INTO DC1..DMAuditHistory (ProjectID,ProposalID,SolutionID,RequestID,AuditFieldTypeID,SrNo,Dateaudited,

  AuditFieldvalue,DrawingStatus,CommerceId,DeleteFlag,AddedBy,DateAdded,ChangedBy,dateChanged,GlobalUniqueID)

  SELECT * FROM DMAuditHistory with(nolock) WHERE ProjectId = @projectId

  SET IDENTITY_INSERT DMAuditHistory OFF

  GO

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/