Questions tagged [merge]
A SQL statement that can conditionally insert, update, or delete target rows.
175 questions
3
votes
1
answer
133
views
MERGE causes unique constraint violation when source contains key multiple times
Given two tables counter and cnt_source defined like this:
create temporary table counter (key bigint, count bigint);
create unique index counter_key_uniq on counter (key);
create temporary table ...
- 31
5
votes
2
answers
845
views
Which approach to concurrency safe upserts is best for table-valued input if MERGE is forbidden?
This classic concurrency safety article is clearly designed for only upserting one row at a time. In my situation, I have a table-valued input and I want to upsert each row in a concurrency safe way. ...
- 1,362
0
votes
1
answer
451
views
Do I need a `HOLDLOCK` on a `MERGE` that's not sourcing from another table?
I need to know whether the following pattern requires me to use HOLDLOCK in a very highly concurrent environment. Particularly note that the source is not another table. It's basically parameters that ...
- 103
8
votes
2
answers
624
views
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 ...
- 727
0
votes
1
answer
88
views
Two SQL databases combined [closed]
I have an accounting firm client that recently acquired another firm. They both use the IDENTICAL software products that utilize a SQL server, so the table structure should be identical or nearly ...
5
votes
1
answer
503
views
Merge Delete on joined tables is allowed but has a bug
To start off, an updatable CTE, derived table or view may be the target of an UPDATE directly, even if it has multiple base tables, as long as all columns are from the same source table.
But they ...
- 18.1k
1
vote
1
answer
164
views
What are the idiomatic approaches to cross-server upserts?
This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following:
You have two T-SQL servers, Production and Reporting.
...
- 1,362
0
votes
1
answer
68
views
Delete records from table that are not in the file being inserted
I have a Snaplogic pipeline that queries a web application every night and loads the data into a SQL Server table. I use the 'Update' snap to add new records or add any changes. The goal is that the ...
- 291
0
votes
1
answer
39
views
Optimize import data from one table into another
Edited on mustaccio request.
I have simple table:
-- DROP TABLE IF EXISTS public.objects;
CREATE TABLE IF NOT EXISTS public.objects
(
id integer NOT NULL DEFAULT nextval('objects_id_seq'::...
- 1
0
votes
1
answer
949
views
Outputting source column in MERGE OUTPUT clause
I have a relatively simple question with an implied answer, but not an explicit one. Here's the background.
Here are the 3 schemas I'm working with:
--Source Data:
ProjectID, ProjectName, CompanyName
...
- 117
0
votes
1
answer
226
views
MongoDB: How to merge a BSON backup with a '.wt' back-up
I am trying to merge a mongodb deployment with a previous back-up due to some processing that took place on the data. I have downloaded and extracted the backup to recover the '.wt' files and can set ...
4
votes
2
answers
2k
views
Is it possible to update an existing table in a single statement, using the output of a MERGE statement?
I know I can achieve this in many ways - this is a purely academic question to see if it's possible to do this in a single statement, in order to further my knowledge of SQL.
We're splitting an old, ...
- 390
2
votes
0
answers
26
views
Merge Replication in SQL2019 database is restored to new server NOT keeping_replication
Merge Replication in SQL2019 database is restored to new server NOT keeping_replication but in restored database we find MSmerge_cont_% system views.
The last part of the system view name appears to ...
3
votes
1
answer
2k
views
MS SQL Server Merge statement does not update NULL values
I have two tables, one in database db1 and another one in db2. They both are identical and only have id, name (varchar) and data (float).
I do this query to move the data update source table in db1 to ...
- 135
0
votes
1
answer
114
views
SQL Server Merge Replication bulk delete leaves subscriber databases large
We have SQL Server 2017 merge replication set up with three subscribers (running SQL Server 2017 Express).
We need to clear data from the server database occasionally to reduce the database size at ...
