SQL Server : update and insert in one query

Question

I have some trouble trying to create an update/insert query.

From a .CSV, I create a temporary table (heading and one datarow as a example):

sku	product_id	description_en	description_ru	description_lv
EE1010	4633	Description in Eng	Description in Rus	Description in Lat

I intend to iterate over each row and update/insert rows into another table with this query:

UPDATE ProductLocalized
SET FullDescription = (CASE 
                          WHEN LanguageID = 7 THEN description_en
                          WHEN LanguageID = 12 THEN description_ru
                          WHEN LanguageID = 14 THEN description_lv
                       END)
WHERE LanguageID IN (7, 12, 14) 
  AND ProductID = product_id;

My problem is how to add the INSERT part if some of the languages missing?

Answer 1

You can use Upsert in SQL to achieve this. Please find below quick example for the same.

create table dbo.test_source
(
id int identity(1,1),
language varchar(50),
description varchar(100)
)
create table dbo.test_dest
(
id int identity(1,1),
language varchar(50),
description varchar(100)
) 

Insert into dbo.test_source  values ('English', 'British language')
Insert into dbo.test_source values ('Hindi', 'Indian language')
Insert into dbo.test_source values ('Chinese', 'China')
Insert into dbo.test_dest  values ('English', 'British language')
Insert into dbo.test_dest values ('Hindi', 'NA') 

SELECT * FROM dbo.test_Source
SELECT * FROM dbo.test_Dest

Result
id          language                                           description
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           English                                            British language
2           Hindi                                              Indian language
3           Chinese                                            China



id          language                                           description
----------- ------------------ -------------
1           English                                            British language
2           Hindi                                              NA


MERGE  dbo.test_dest  as MyTarget
USING  
(
    SELECT 
    ID,
    Language,
    Description
    FROM  dbo.test_source
) as MySource
ON MyTarget.Language = MySource.Language 
WHEN MATCHED AND NOT 
    (
        MySource.Description = ISNULL(MyTarget.Description, '')    
    )
THEN  
    UPDATE 
    Set MyTarget.Description = MySource.Description      
WHEN NOT MATCHED BY TARGET
THEN  
    INSERT (Language, description)
    VALUES (MySource.Language
    ,MySource.Description);


SELECT * FROM dbo.test_Dest


Result


id          language                                           description
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           English                                            British language
2           Hindi                                              Indian language
3           Chinese                                            China

We can see record with 2 got updated with source table description and record with id 3 got inserted as it was not exist into destination table.

Answer 2

You can use IF EXIST or IF NOT EXIST statements to filter the records and then apply the INSERT or UPDATE Commands.

Example:

IF NOT EXIST ( Condition ) { INSERT }

IF EXIST ( Condition ) { UPDATE }

An alternate way is:

IF EXIST( Condition )

 update 

ELSE

 insert