Violation of PRIMARY KEY constraint 'OHEM_PRIMARY'. Cannot insert duplicate key in object

• rizkhan92

  SSC Veteran

  Points: 299

  More actions

  January 5, 2011 at 2:46 am

  #232582

  Declare @EmpID int

  Declare @FName nvarchar(50)

  Declare @MName nvarchar(20)

  Declare @LName nvarchar(20)

  Declare @JTitle nvarchar(250)

  Declare @EmpCode nvarchar(150)

  Declare @OfficeTel nvarchar(150)

  Declare @mobile nvarchar(100)

  --Declare @pager nvarchar(100)

  Declare @hometel nvarchar(100)

  Declare @manager int

  Declare @fax nvarchar(100)

  Declare @email nvarchar(100)

  Declare @Work_Street nvarchar(100)

  Declare @Work_block nvarchar(100)

  Declare @Work_city nvarchar(100)

  Declare @Work_Zip nvarchar(100)

  Declare @Work_Country nvarchar(100)

  Declare @home_Street nvarchar(100)

  Declare @home_block nvarchar(100)

  Declare @home_city nvarchar(100)

  Declare @home_Zip nvarchar(100)

  Declare @home_Country nvarchar(100)

  Declare @StartDate datetime

  Declare @status nvarchar(50)

  Declare @termdate datetime

  Declare @Sex nvarchar(10)

  Declare @birthdate datetime

  Declare @martstatus nvarchar(10)

  Declare @govID nvarchar(100)

  Declare @Salary money

  Declare @bankcode nvarchar(200)

  Declare @bankAccount nvarchar(100)

  Declare @bankBranch nvarchar(100)

  Declare @EmpName nvarchar(500)

  Declare @DeptCode nvarchar(100)

  Declare @DesgCD nvarchar(100)

  Declare @CAddress1 nvarchar(500)

  Declare @CAddress2 nvarchar(500)

  Declare @PAddress1 nvarchar(500)

  Declare @PAddress2 nvarchar(500)

  Declare @branch nvarchar(200)

  Declare @IndexofSpace int

  Declare @IndexofSpace2 int

  Declare @U_Name nvarchar(200)

  Declare @TempName nvarchar(200)

  Declare @SecondThirdName nvarchar(200)

  DECLARE C5 CURSOR

  FOR

  Select Emp_Cd,Emp_Nm,Birth_Dt,Gender_Cd,

  (Select GMDesgMst.Desg_Nm from GMDesgMst

  Where GMDesgMst.Desg_Cd=GMEmpMst.Desg_Cd) As Designation,Desg_Cd,

  GMEmpMst.Dept_Cd,Join_Dt,Passpt_Cd,

  Emp_Cat_Cd As Emp_Status,

  GMEmpMst.Branch_Cd,Marital_Cd,Cr_Add1 ,Cr_Add2 ,Cr_Pincode,

  Pr_Add1,Pr_Add2,Pr_Pincode,Phone1,Phone2,mobile,Email,SalBankNm,

  SalBankBranch,Salary_Ac_No,Present_CTC,PanNo As ContractEndDate

  From GMEMPMST

  OPEN C5

  Set @EmpID=1

  FETCH NEXT FROM C5

  INTO @EmpCode,@EmpName,@birthdate,@Sex,@JTitle,@DeptCode ,@DesgCD,@StartDate,

  @govID,@Status,@branch,

  @martstatus,@CAddress1,@CAddress2,@Work_Zip,

  @PAddress1,@PAddress2,@home_Zip,

  @OfficeTel,@hometel,@mobile,

  @email,@bankcode,@bankBranch,@bankAccount,

  @Salary,@termdate

  WHILE @@FETCH_STATUS = 0

  BEGIN

  If Not Exists(Select empid from SBO_Rehnuma.dbo.OHEM

  where SBO_Rehnuma.dbo.OHEM.jobTitle=@EmpCode)

  Begin

  --Select @DeptCode

  Set @DeptCode=(Select SBO_Rehnuma.dbo.OUDP.Code from SBO_Rehnuma.dbo.OUDP

  Where SBO_Rehnuma.dbo.OUDP.U_HRMSCode=@DeptCode)

  ----Select @DesgCode

  Set @DesgCD=(Select SBO_Rehnuma.dbo.OHPS.Name from SBO_Rehnuma.dbo.OHPS

  Where SBO_Rehnuma.dbo.OHPS.Name=@DesgCD)

  --Select @DesgCD

  --Select @DeptCode

  Set @bankcode=(Select top 1 T1.BankCode From SBO_Rehnuma.dbo.ODSC As T1

  Where T1.BankName Like @bankcode)

  Set @branch=(Select SBO_Rehnuma.dbo.OUBR.Code from SBO_Rehnuma.dbo.OUBR

  Where SBO_Rehnuma.dbo.OUBR.Remarks=@branch)

  --Select @status

  Set @status= (Select statusID From SBO_Rehnuma.dbo.OHST where descriptio Like @status)

  Set @U_Name= @empname

  Set @IndexofSpace= CharIndex(' ',@EmpName ,0)

  if (@IndexofSpace> 0)

  Begin

  Set @FName = SubString(@EmpName,0,@IndexofSpace)

  Set @SecondThirdName = SubString(@EmpName,@IndexofSpace+1,len(@Empname))

  Set @IndexofSpace2 = CharIndex(' ',@SecondThirdName,0)

  if(@IndexofSpace2 > 0 )

  Begin

  Set @MName = SubString (@SecondThirdName,0,@IndexofSpace2)

  Set @LName = SubString(@SecondThirdName,@IndexofSpace2,len(@Empname))

  End

  Else

  Begin

  Set @MName = @SecondThirdName

  Set @LName = Null

  End

  End

  Else

  Begin

  Set @FName= @EmpName

  Set @MName = Null

  Set @LName = Null

  End

  Insert Into SBO_Rehnuma.dbo.OHEM(empid,jobTitle,

  firstName,middleName,lastName,

  sex,dept,branch,

  workStreet,workBlock,workZip,workCountr,

  officeTel,mobile,homeTel,

  email,startDate,status,

  salary,termDate,

  bankCode,bankBranch,bankAcount,

  homeStreet,homeBlock,homeZip,

  homeCountr,birthDate,

  govID,U_JobTitle,U_EmpName,position)

  Values(@EmpID,@EmpCode,

  @FName,@MName,@LName,@Sex,@DeptCode,@branch,

  @CAddress1,@CAddress2,@Work_Zip,'PK',

  @OfficeTel,@hometel,@mobile,@email,@StartDate,

  @status,@Salary,@termdate,@bankcode,@bankBranch,@bankAccount,

  @PAddress1,@PAddress2,@home_Zip,'PK',

  @birthdate,@govID,@JTitle,@U_Name,@DesgCD)

  End

  Set @EmpID=@EmpID+1

  FETCH NEXT FROM C5

  INTO @EmpCode,@EmpName,@birthdate,@Sex,@JTitle,@DeptCode ,@DesgCD,@StartDate,

  @govID,@Status,@branch,

  @martstatus,@CAddress1,@CAddress2,@Work_Zip,

  @PAddress1,@PAddress2,@home_Zip,

  @OfficeTel,@hometel,@mobile,

  @email,@bankcode,@bankBranch,@bankAccount,

  @Salary,@termdate

  END

  CLOSE C5

  DEALLOCATE C5

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  January 5, 2011 at 2:50 am

  #1269362

  Duplicate post, please send all replies to:

  http://www.sqlservercentral.com/Forums/Topic1042892-391-1.aspx

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA