CODEWITHSUNDEEP
sql
S I
S I

Reputation: 80

Insert and Update multiple values in single SQL Statement

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

Upvotes: 1

Views: 1065

Answers (1)

Praveen
Praveen

Reputation: 9335

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

Upvotes: 3

Related Questions