Cannot Insert duplicate key row

  • Hi All

    I need some assistance with a cursor,

    I'm pulling data from another server/table and inserting missing data in another table but the trick now is that if it finds data exist it must update if it's different from the source rather than inserting the same record. I hope the above it's clear see code below.

    Tx!!

    Declare @Stage_Custid varchar (max), @HEAT_Custid varchar (max),@S_First_Name varchar (max),

    @S_Last_Name varchar (max), @S_Company varchar (max), @S_Business_Unit varchar (max),

    @S_Business_Unit_Description varchar (max),@S_Department varchar (max),

    @S_Department_Description varchar (max), @S_Job_Code_Description varchar (max),

    @S_Office varchar (max),@S_Office_Description varchar (max), @S_State_Business varchar (max),

    @S_Cell_Number varchar (max), @S_Work_Number varchar (max), @S_Email_address varchar (max),

    @S_Logon_Name varchar (max), @S_Building varchar (max)

    Declare D_Check_Exists_A Cursor Fast_forward for

    Select distinct(Personel_Nr)

    from [server].db.dbo.STG_HEAT

    Open D_Check_Exists_A

    Fetch Next from D_Check_Exists_A into @Stage_Custid

    While @@Fetch_Status = 0

    Begin

    IF exists

    ( Select *

    from [server].db.dbo.Profile

    where Custid = @Stage_Custid and

    Custtype = 'Customers' and

    Custid like 'P%' and (DO_NOT_SYNC_PSHR is null or DO_NOT_SYNC_PSHR <> 'T'))

    /* Update Existing Profiles */

    Begin

    Select

    @S_First_Name=Employee_First_name,

    @S_Last_Name=Employee_Last_Name,

    @S_Department_Description=Department_Description,

    @S_Cell_Number=Cell_Phone_Number,

    @S_Work_Number=Work_Number,

    @S_Email_Address=Email_Address,

    @S_Job_Code_Description=Job_Code_Description,

    @S_Business_Unit=Business_Unit,

    @S_Business_Unit_Description=Business_Unit_Description,

    @S_Department_Description=Department_Description,

    @S_Department=Department,

    @S_Office_Description=Office_Description,

    @S_Office=Office,

    @S_Building=Building

    From

    [server].db.dbo.STG_HEAT

    Where

    Personel_Nr=@Stage_Custid

    Update [server].db.dbo.Profile

    Set

    LastName=@S_Last_Name,

    FirstNames=@S_First_Name,

    Dept=@S_Department_Description,

    CellPhone=@S_Cell_Number,

    Ext=@S_Work_Number,

    EmailAddr=@S_Email_Address,

    Designation=@S_Job_Code_Description,

    BU_Desc=@S_Business_Unit_Description,

    BU_Dept_Desc=@S_Department_Description,

    BU_Dept_Code=@S_Department,

    BU_Code=@S_Business_Unit,

    BU_Region=@S_Office_Description,

    BU_Region_Code=@S_Office,

    Building=@S_Building

    Where

    Custid = @Stage_Custid

    End

    Else

    Begin

    /* Populate Variables */

    Select

    @S_First_Name=Employee_First_name,

    @S_Last_Name=Employee_Last_Name,

    @S_Department_Description=Department_Description,

    @S_Cell_Number=Cell_Phone_Number,

    @S_Work_Number=Work_Number,

    @S_Email_Address=Email_Address,

    @S_Job_Code_Description=Job_Code_Description,

    @S_Business_Unit=Business_Unit,

    @S_Business_Unit_Description=Business_Unit_Description,

    @S_Department_Description=Department_Description,

    @S_Department=Department,

    @S_Office_Description=Office_Description,

    @S_Office=Office,

    @S_Building=Building

    From

    [server].db.dbo.STG_HEAT

    Where

    Personel_Nr=@Stage_Custid

    /* Insert Missing Profiles */

    Insert into [server].db.dbo.Profile

    ( Custid,Custtype,LastName,FirstNames,

    Dept,CellPhone,Ext,EmailAddr,Designation,

    BU_Desc,BU_Dept_Desc,BU_Dept_Code,BU_Code,

    BU_Region,BU_Region_Code,Building )

    Values

    ( @Stage_Custid, 'Customers', @S_Last_Name, @S_First_Name,

    @S_Department_Description, @S_Cell_Number,

    @S_Work_Number,@S_Email_Address,@S_Job_Code_Description,

    @S_Business_Unit_Description, @S_Department_Description,

    @S_Department,@S_Business_Unit, @S_Office_Description,

    @S_Office, @S_Building)

    End

    --Print 'Inserting '+ @Stage_Custid + ',' + @S_First_Name

    Fetch Next from D_Check_Exists_A into @Stage_Custid

    End

    It's better to fail while trying, rather than fail without trying!!!

  • There is no need to use a cursor. You can use an update and insert statement directly on the tables. Here is a small demo that shows how to do it:

    use tempdb

    go

    --creating the demo tables

    create table source (id int, data char(1))

    go

    create table target (id int, data char(1))

    go

    --Inserting data into the tables

    insert into source (id, data)

    select 1, 'a'

    union

    select 3,'c'

    union

    select 4,'d'

    insert into target (id, data)

    select 1,'b' --should be modified

    union

    select 2,'b'

    union

    select 4,'d'

    union

    select 5,'e'

    --Inserting the missing records according to the column id

    --I use left join and whenever a key exists on the source

    --table but not on the target table, it is a record that

    --should be inserted as new record

    insert into target (id, data)

    select s.id, s.data

    from source s left join target t on s.id = t.id

    where t.id is null

    --Updating the recods that exist on both table.

    --I use inner join to get the records that exist

    --on both tables

    update t

    set t.data = s.data

    from target t inner join source s on t.id = s.id

    --Check the results of both operations

    select * from target

    go

    drop table source

    go

    drop table target

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Tx, i will test the scenario.

    It's better to fail while trying, rather than fail without trying!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply