Merge statement to delete/update/insert data in Sql Server

477 Views Asked by At

I have an excel file with thousand of rows which I need to use to delete/update/insert some tables. The excel provides the following data: provider_id, country_name, locale, property1, property2. The tables which need to be updated are: provider_country with columns : provider_country_id, provider_id, country_id, property1, property2 and provider_country_language with columns : provider_country_language_id, provider_country_id, language_id. I can also use table country with columns (for joins): country_id, country_name. And table language with columns: language_id, locale, country_id. The fields which need to be updated are country_id, language_id, property1,property2 (from provider_country and provider_country_language)

I have created a temporary table with all the data from the excel:

CREATE TABLE #TempProviderCountryLanguage(
[provider_id] int NULL,
[country_name] nvarchar(50) NULL,
[locale] nvarchar(10) NULL,
[property1] int NULL,
[property2] decimal(5,2) NULL
) 

INSERT INTO #TempProviderCountryLanguage VALUES 
(1,N'Provider1',N'Brazil',N'en-br',4,NULL)
INSERT INTO #TempProviderCountryLanguage VALUES 
(1,N'Provider1',N'Brazil',N'pt-br',4,NULL)
INSERT INTO #TempProviderCountryLanguage VALUES 
(1,N'Provider1',N'Denmark',N'da-dk',4,12.21)
INSERT INTO #TempProviderCountryLanguage VALUES 
(2,N'Provider2',N'Denmark',N'da-dk',5,14.21)
......

MERGE [provider_country] AS TARGET
USING (
SELECT tb.provider_id
    ,c.country_id
    ,l.language_id
    ,tb.property1
    ,tb.property2
FROM #TempProviderCountryLanguage tb
INNER JOIN country c ON c.country_name = tb.country_name
INNER JOIN language l ON l.locale = l.locale
) AS SOURCE
ON (
        TARGET.provider_id = SOURCE.provider_id AND
        TARGET.country_id = SOURCE.country_id
        )
WHEN MATCHED
THEN
    UPDATE
    SET TARGET.country_id = SOURCE.country_id,
        TARGET.property1 = SOURCE.property1,
        TARGET.property2 = SOURCE.property2
WHEN NOT MATCHED BY TARGET
THEN
    INSERT (
        provider_id
        ,country_id
        ,property1
        ,property2
        )
    VALUES (
        SOURCE.provider_id
        ,SOURCE.country_id
        ,SOURCE.property1
        ,SOURCE.property2
        )
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

For the provider_country_language I plan to make another merge.

I am trying to update the tables using merge but I have a problem because I cannot make a unique select here (somehow I would need the language_id as well):

ON (
TARGET.provider_id = SOURCE.provider_id AND
TARGET.country_id = SOURCE.country_id
)

And the error is :

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

How can I make this work and make sure all the tables are updated correctly?(not necessarily using Merge) And from performance point of view what would be the best approach? (only the INSERT INTO will be performed thousand of times...)

0

There are 0 best solutions below