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?

You can't INSERT and UPDATE in the same statement, apart from with a MERGE. If you don't want to use a MERGE then you would use an "upsert", which is an UPDATE statement followed by an INSERT statement (a search will show you how to write these). — Thom A
– Thom A ♦, Commented Jul 14, 2021 at 12:10
for use merge see this link mssqltips.com/sqlservertip/1704/… — Hamid Taebi
– Hamid Taebi, Commented Jul 14, 2021 at 12:10
I strongly suggest you normalize this table into ProductDescription with columns ProductID, LanguageID, Description. Then you can do a normal joined merge — Charlieface
– Charlieface, Commented Jul 14, 2021 at 13:12
Are you attempting to update or insert multiple rows in the target from each single row in your source? I.e., you need to add/update the russian row, the english row, and the lat(vian) rows that match product ID? — SMor
– SMor, Commented Jul 14, 2021 at 13:44

Jaydeep Suryawanshi · Accepted Answer · 2021-07-14 13:06:05Z

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.

Sachin Tripathi · Accepted Answer · 2021-07-14 12:43:14Z

0

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

answered Jul 14, 2021 at 12:43

Sachin Tripathi

1411 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL Server : update and insert in one query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related