SQL if not exists insert else update

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

No indexes atm, I just want to get it working to send over the data first — user3478524, May 30 '14 at 12:50

GarethD · Answer 1 · 2014-05-30T13:02:38.963

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.

Thanks Gareth this looks much more efficient – user3478524 May 30 '14 at 12:58 — user3478524, May 30 '14 at 12:58

mordack550 · Answer 2 · 2014-05-30T13:06:10.303

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);

score 0 · Answer 3 · answered May 30 '14 at 12:54

BEGIN TRY
      SET NOCOUNT ON
      DECLARE @l_numberOfRecordsInserted INT       
         BEGIN
               RAISERROR ( N'Invalid Application User ID %d passed.'
               ,17
               ,1
               ,-1 )
         END
      IF NOT EXISTS ( SELECT 
                          1
                      FROM
                          Data_Archive.dbo.Company
                      WHERE
                          ( CompanyID  = @i_CompanyID  )
                          AND CompanyName = @vc_CompanyName ) 
         BEGIN
               INSERT INTO
                   Data_Archive.dbo.Company
                   (
                    CompanyID,
                    CompanyName,
                    Address1,
                    Address2,
                    Address3,
                    Address4,
                    ZipCode
                   )
            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 = @i_CompanyID)


         END
      ELSE
         BEGIN
              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 = @i_CompanyID

         END

      RETURN 0
END TRY        
--------------------------------------------------------         
BEGIN CATCH        
    -- Handle exception        
      DECLARE @i_ReturnedErrorID INT
      EXECUTE @i_ReturnedErrorID = dbo.usp_HandleException @i_UserId = @i_AppUserId

      RETURN @i_ReturnedErrorID
END CATCH

score 0 · Answer 4 · answered May 30 '14 at 13:00

You can try using a Merge statement instead. I believe this should work for you:

Merge Into Data_Archive.dbo.Company As Target
Using
(
    Select  CompanyID,
            CompanyName,
            Address1,
            Address2,
            Address3,
            Address4,
            ZipCode
    From    OLD.dbo.company 
) As Source On  Source.CompanyID = Target.CompanyID
When Not Matched Then 
    Insert (CompanyID, CompanyName, Address1, Address2, Address3, Address4, ZipCode)
    Values (Source.CompanyID, Source.CompanyName, Source.Address1, Source.Address2, Source.Address3, Source.Address4, Source.ZipCode)
When Matched Then
    Update
    Set     CompanyName = Source.CompanyName,
            Address1    = Source.Address1,
            Address2    = Source.Address2,
            Address3    = Source.Address3,
            Address4    = Source.Address4,
            ZipCode     = Source.ZipCode
;

SQL if not exists insert else update

4 Answers4