SQL if not exists insert else update [duplicate]

Question

I Have a stored procedure that works below:

--Stored procedure to update data in Data_Archive.dbo.company
CREATE PROC dbo.TransferCompanyInfo
as
BEGIN TRAN
INSERT INTO Data_Archive.dbo.Company
Select 
CompanyID,
CompanyName,
Address1,
Address2,
Address3,
Address4,
ZipCode,
FROM OLD.dbo.company 
where not exists (select Data_Archive.dbo.Company.CompanyID from                   Data_Archive.dbo.Company
WHERE CompanyID = OLD.dbo.Company.CompanyID)

Update Data_Archive.dbo.Company
Set 
Data_Archive.dbo.Company.CompanyID = b.CompanyID,
Data_Archive.dbo.Company.CompanyName = b.CompanyName,
Data_Archive.dbo.Company.Address1 = b.Address1,
Data_Archive.dbo.Company.Address2 = b.Address2,
Data_Archive.dbo.Company.Address3 = b.Address3,
Data_Archive.dbo.Company.Address4 = b.Address4,
Data_Archive.dbo.Company.ZipCode = b.ZipCode,
From Data_Archive.dbo.Company a, OLD.dbo.Company b
Where a.CompanyID = b.CompanyID


IF @@ERROR <> 0
            BEGIN
                ROLLBACK TRAN
                RAISERROR ('Error occured while copying data to TachographData_Archive.dbo.company', 16, 1)
                RETURN -1
           END

 IF @@TRANCOUNT > 0
     BEGIN
           COMMIT TRAN
           RETURN 0
     END

However I was wondering how could I make it faster because at the moment it has to scan table to see if not exists, then insert, then scan again and update. I was thinking if instead I did if exists update else insert it would make it faster but I'm not sure how to reformat it. At the moment I tried the code below but doesn't work:

--Stored procedure to update data in Data_Archive.dbo.company

 CREATE PROC dbo.TransferCompanyInfo
    as
    BEGIN TRAN
    Update Data_Archive.dbo.Company
    Set 
    Data_Archive.dbo.Company.CompanyID = b.CompanyID,
    Data_Archive.dbo.Company.CompanyName = b.CompanyName,
    Data_Archive.dbo.Company.Address1 = b.Address1,
    Data_Archive.dbo.Company.Address2 = b.Address2,
    Data_Archive.dbo.Company.Address3 = b.Address3,
    Data_Archive.dbo.Company.Address4 = b.Address4,
    Data_Archive.dbo.Company.ZipCode = b.ZipCode,
    From Data_Archive.dbo.Company a, OLD.dbo.Company b
    Where a.CompanyID = b.CompanyID

    ELSE
    INSERT INTO Data_Archive.dbo.Company
    Select 
    CompanyID,
    CompanyName,
    Address1,
    Address2,
    Address3,
    Address4,
    ZipCode,
    FROM OLD.dbo.company 
    where not exists (select Data_Archive.dbo.Company.CompanyID from                   Data_Archive.dbo.Company
    WHERE CompanyID = OLD.dbo.Company.CompanyID)



    IF @@ERROR <> 0
                BEGIN
                    ROLLBACK TRAN
                    RAISERROR ('Error occured while copying data to TachographData_Archive.dbo.company', 16, 1)
                    RETURN -1
               END

     IF @@TRANCOUNT > 0
         BEGIN
               COMMIT TRAN
               RETURN 0
         END

Answer 1

You could use MERGE for this:

MERGE Data_Archive.dbo.Company WITH (HOLDLOCK) AS a
USING OLD.dbo.Company AS o
    ON a.CompanyID = b.CompanyID
WHEN MATCHED THEN UPDATE    
    SET CompanyName = o.CompanyName,
        Address1 = o.Address1,
        Address2 = o.Address2,
        Address3 = o.Address3,
        Address4 = o.Address4,
        ZipCode = o.ZipCode
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (CompanyName, Address1, Address2, Address3, Address4, ZipCode)
    VALUES (o.CompanyName, o.Address1, o.Address2, o.Address3, o.Address4, o.ZipCode)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

This will update any records that exist, insert any new records, and delete any records from the archive that have been deleted from the OLD database. If this last bit is not required then simply remove WHEN NOT MATCHED BY SOURCE THEN DELETE.

Using HOLDLOCK will ensure that you do not meet a race condition, although MERGE does still have some quirks, I don't think they will be an issue here.

The main benefit of this is that SQL Manages this in a single transaction, so you don't need to check if one part failed and then roll the transaction back.

Answer 2

Use the MERGE statement:

MERGE INTO Stg.Data_Archive.dbo.Company AS tbl
USING (
    SELECT old.CompanyID, old.CompanyName, old.Address1, old.Address2, old.Address3, old.Address4, old.ZipCode
    FROM OLD.dbo.Company AS old
    LEFT OUTER JOIN Data_Archive.dbo.Company AS new
    ON old.CompanyID = new.CompanyID 
    WHERE new.CompanyID IS NOT NULL) AS source
ON tbl.CompanyID=source.CompanyID
WHEN MATCHED THEN --if i find a row, update
    UPDATE SET
    tbl.CompanyID = source.CompanyID,
    tbl.CompanyName = source.CompanyName,
    tbl.Address1 = source.Address1,
    tbl.Address2 = source.Address2,
    tbl.Address3 = source.Address3,
    tbl.Address4 = source.Address4,
    tbl.ZipCode = source.ZipCode
WHEN NOT MATCHED THEN --if not, insert a new one
    INSERT (CompanyID, CompanyName, Address1, Address2, Address3, Address4, ZipCode)
    VALUES (source.CompanyID, source.CompanyName, source.Address1, source.Address2, source.Address3, source.Address4, source.ZipCode);