Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [merge]

A SQL statement that can conditionally insert, update, or delete target rows.

Officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard, MERGE does an INSERT for new records and an UPDATE of existing records depending on whether or not a condition matches.

Database management systems such as Oracle Database, DB2, Teradata and Microsoft SQL Sever support this syntax. Some allow a DELETE action as well.

MERGE is similar to, but not synonymous with UPSERT.

174 questions
80
votes
1 answer

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It is important to specify only the columns from the…
KutuluMike
  • 1,599
  • 2
  • 14
  • 13
47
votes
2 answers

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john if there are records with id 30122 then I have…
SpringLearner
  • 611
  • 1
  • 5
  • 9
25
votes
1 answer

Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table

I found again an issue with SQL Server and MERGE statement and need some confirmation. I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019). Please execute following steps (step by step, not in one…
Daniel C.
  • 353
  • 3
  • 6
24
votes
4 answers

Merge statement deadlocking itself

I have the following procedure (SQL Server 2008 R2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set nocount, xact_abort on; merge CompanyUser with…
Sako73
  • 435
  • 3
  • 5
  • 8
24
votes
1 answer

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" FROM @p1 AS d JOIN "MySchema"."Region"…
Mr.Brownstone
  • 13,242
  • 4
  • 38
  • 55
21
votes
1 answer

Use Where Clause With Merge

In the below syntax with the When Matched is it possible to only update if the value in the update table differs from the value in the employee table? Something similar to my below DDL - but of course this throws an error and does not work. What…
SmallFries BigGuys
  • 509
  • 5
  • 12
  • 22
17
votes
1 answer

MERGE deadlocking prevention

In one of our databases we have a table that is intensively concurrently accessed by multiple threads. Threads do update or insert rows via MERGE. There are also threads that delete rows on occasion, so table data is very volatile. Threads doing…
i-one
  • 2,374
  • 2
  • 14
  • 23
13
votes
1 answer

What are my locking options for a Merge statement?

I have a stored procedure that performs a MERGE statement. It seems like it locks the whole table by default when performing the merge. I'm calling this stored procedure inside of a transaction where I'm also doing some other stuff and I wish it…
John Buchanan
  • 233
  • 1
  • 2
  • 6
12
votes
2 answers

Is a MERGE with OUTPUT better practice than a conditional INSERT and SELECT?

We often encounter the "If not exists, insert" situation. Dan Guzman's blog has an excellent investigation of how to make this process threadsafe. I have a basic table that simply catalogs a string to an integer from a SEQUENCE. In a stored…
Matthew
  • 1,693
  • 2
  • 17
  • 27
11
votes
3 answers

MERGE into a view with INSTEAD OF triggers

I have a view with instead of triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of merge statement. Here's my table and view: create table tbl (id uniqueidentifier not null primary key, data…
torvin
  • 213
  • 2
  • 7
10
votes
2 answers

Difference between UPSERT and MERGE?

From the PostgreSQL wiki, MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE,…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
10
votes
1 answer

Merge row size overflow in SQL Server - "Cannot create a row of size.."

The target table to which I'm trying to merge the data has ~660 columns. The code for the merge: MERGE TBL_BM_HSD_SUBJECT_AN_1 AS targetTable USING ( SELECT * FROM TBL_BM_HSD_SUBJECT_AN_1_STAGING WHERE [ibi_bulk_id] in…
Yosi Dahari
  • 517
  • 5
  • 15
8
votes
2 answers

Blocking between two MERGE queries inserting into the same table

Scenario I have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other. Sample code to recreate the scenario: 1. Preparation. Create tables…
ToC
  • 727
  • 1
  • 8
  • 20
8
votes
1 answer

MERGE with OUTPUT doesn't seem to be doing the right thing

I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command: MERGE ModifiedTable t1 USING TargetTable tt ON…
thecoop
  • 403
  • 1
  • 4
  • 7
8
votes
1 answer

Is using multiple unique constrains on a single table considered bad design?

I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name,…
szabkel
  • 264
  • 2
  • 11
1
2 3
…
11 12