Insert and Update multiple values in single SQL Statement

Question

We are using Insert statements for multi inserts like this:

INSERT INTO [db1].[dbo].[tb1] ([ID], [CLM1], [CLM2]) 
VALUES 
('1', "A", "DB"),
('2', "AB", "BQ"),
('3', "AA", "BH"),
('4', "AD", "BT"),
('5', "AF", "EB"),
('6', "EA", "AB")

In the above table, ID is primary key, want to know one query with passing all values, values should update existing records and insert new records into table

For SQL Server there is the MERGE command... but it is a pain... — xanatos
– xanatos, Commented Aug 13, 2015 at 11:43

Praveen · Accepted Answer · 2015-08-13 11:57:03Z

3

You can use Merge:

MERGE INTO [db1].[dbo].[tb1] AS Target
USING (
    VALUES 
    ('1', 'A', 'DB'), 
    ('2', 'AB', 'BQ'), 
    ('3', 'AA', 'BH'), 
    ('4', 'AD', 'BT'), 
    ('5', 'AF', 'EB'), 
    ('6', 'EA', 'AB')
) AS Source (new_ID, new_CLM1, new_CLM2)
ON Target.ID = Source.new_ID
WHEN MATCHED THEN
UPDATE SET 
    ID = Source.new_ID,
    CLM1 = Source.new_CLM1,
    CLM2 = Source.new_CLM2
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, CLM1, CLM2) VALUES (new_ID, new_CLM1, new_CLM2);

Merge Doc

answered Aug 13, 2015 at 11:57

Praveen

9,4534 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

S I Over a year ago

thank you very much, its working exactly how we wanted to be, thank you very much

Collectives™ on Stack Overflow

Insert and Update multiple values in single SQL Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related