Questions tagged [merge]

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

Filter by
Sorted by
Tagged with
5 votes
1 answer
258 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 ...
Charlieface's user avatar
  • 12.8k
1 vote
1 answer
115 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. ...
J. Mini's user avatar
  • 123
0 votes
1 answer
29 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 ...
Matt's user avatar
  • 271
0 votes
1 answer
27 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'::...
Solmorth's user avatar
0 votes
1 answer
421 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 ...
Vocoder's user avatar
  • 117
0 votes
1 answer
70 views

Update or insert records to the same table based on a similar table but with conditions

I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table. I have 2 tables say one is POV [pov, dc, item](the ...
Akash Reddy's user avatar
0 votes
1 answer
134 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 ...
mspen_golf's user avatar
2 votes
2 answers
873 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, ...
Ian Kemp's user avatar
  • 364
2 votes
0 answers
17 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 ...
Rick Willemain's user avatar
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 ...
Dmitry Sazhnev's user avatar
0 votes
1 answer
43 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 ...
Steve Colley's user avatar
3 votes
1 answer
2k views

HOLDLOCK required in MERGE with UPDATE

Within a .NET 6 application the EntityFramework is used to keep track of entities and to update related fields. To improve bulk updates, the EfCore.BulkExtensions package has been added. During a ....
Odrai's user avatar
  • 143
26 votes
1 answer
2k views

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 ...
Daniel C.'s user avatar
  • 363
5 votes
1 answer
739 views

Minimise duration of table lock

I've got a SQL table that needs to be updated daily. There may or may not be queries against that table while the update is happening. It's around 500,000 rows. We have an issue where there is locking ...
the Ben B's user avatar
2 votes
1 answer
2k views

What is the difference between MERGE vs. MERGE INTO?

What is the difference between MERGE vs. MERGE INTO? What are the use cases for each? Surprisingly, I couldn't find any existing question that asks about this difference. The documentation for MERGE ...
HeyJude's user avatar
  • 447
5 votes
1 answer
639 views

Why does UPDATE FROM fail and MERGE works on a View with an INSTEAD OF Trigger?

Sometimes for refactoring, I use a View to abstract the changes and use INSTEAD OF Triggers to mimic the previous functionality. I've run into error 414 (or 415) in the past UPDATE is not allowed ...
Zikato's user avatar
  • 4,497
0 votes
1 answer
169 views

merging nearly- duplicate rows from different databases in the same table

I am sure , that many people before me faced a need to merge nearly- duplicate rows from several databases. However, I have not been able to find a simple yet general solution. I provided a data ...
YW T's user avatar
  • 11
0 votes
1 answer
523 views

MSSQL. Is it possible to execute bulk MERGE ignoring failures?

Suppose we have a simple relationship between two tables (Products and Categories) And they contain following data: And I want to do a SQL MERGE operation on the dbo.Products table from a temporary ...
Cristian E.'s user avatar
11 votes
3 answers
2k views

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 ...
torvin's user avatar
  • 213
0 votes
1 answer
311 views

pg_dump -> import traject using "upsert" paradigm?

Well say I have two physically separate database and I wish to replace database B with data from A. Except that I wish to keep the data in B which does not exist in A? So basically I'd go over each ...
paul23's user avatar
  • 473
0 votes
1 answer
466 views

sqlserver - Insert from xml generated with `select for xml`

Is there a simple way to create an XML (file, variable, value in column, whatever) with select * from table for xml ... and later automatically insert this data into identical table in different ...
Jędrzej Dudkiewicz's user avatar
1 vote
1 answer
1k views

"merge" two tables -from different databases- together?

Well say I have a table running on a database at localhost (donor database A), and a receiving table running at another server externally (database B) With table creation (on both sides) like: CREATE ...
paul23's user avatar
  • 473
0 votes
0 answers
399 views

Why SQL Server's MERGE has such a huge Excessive grant when compared to INSERT + DELETE?

I am trying to optimize a security computation process for a Web application by trying to making it synchronous (compute on the fly for an entity) instead of asynchronous (large computation for all ...
Alexei's user avatar
  • 1,261
1 vote
1 answer
975 views

Receiving error "invalid identifier" while trying to update a table in Oracle

I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed. Tables structure: create table TEST_FILE_MERGE ( customer_num NUMBER, ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
2k views

Insert/update loop ORACLE function

I want to find the best solution for an insert/update oracle function. My task is to collect some date from multiple tables (source tables) and bring them to a single table (destination table). Source ...
Luc's user avatar
  • 1
0 votes
3 answers
4k views

Can I simplify this MERGE statement w.r.t. WHEN MATCHED conditions?

I'm using SQL Server (SQL Server 2016 and Azure SQL) and I have this MERGE statement which uses a rather gnarly WHEN MATCHED condition to only update rows where values are actually different. This is ...
Dai's user avatar
  • 574
0 votes
1 answer
643 views

T-SQL Merge not updating in explicit transaction

We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the ...
cenko2's user avatar
  • 1
0 votes
1 answer
2k views

JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas. Basically, what I want to do seems like a UNION where records are "collapsed" in ...
tapehead's user avatar
0 votes
1 answer
689 views

Can I MERGE INTO ... INSERT with a column list in Hive?

Here's a good example of a MERGE statement: MERGE INTO target AS T USING source AS S ON T.ID = S.ID and T.tran_time = S.tran_time WHEN MATCHED UPDATE SET quantity = S.quantity WHEN MATCHED AND S....
PhilHibbs's user avatar
  • 539
0 votes
1 answer
291 views

SQL: Merge rows when value doesnt change in continuous time

I am new to SQL and I need to summarize a table that has this information: Serial Number, StartDate, EndDate, StatusID and TimeInState, what I need is to merge rows that keep the same StatusId during ...
Javier Santiago Cáceres's user avatar
1 vote
2 answers
6k views

It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL?

I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime. I don't expect the past data from select to change overtime, the ...
Glasnhost's user avatar
  • 478
1 vote
0 answers
38 views

Using Merge in Oracle to update a table and clear out a field if the record is not in the source table

I need some help converting an MSSQL Merge statement into Oracle MERGE INTO A USING B on a.a_id = b.b_id WHEN MATCHED and isNull(a.a_field, '') <> isNull(b.b_field,'') THEN UPDATE SET a....
Sandy DeLeon's user avatar
1 vote
0 answers
184 views

Integrating Flyway and Github [closed]

I was wondering if any of you can share some experience regarding integrating Flyway and Git. We're currently developing a project, each sprint we need to make database changes for new features of ...
browsingThrough91's user avatar
0 votes
1 answer
21 views

Merging table records across TabName for each PersonID

Consider the following table, CREATE TABLE temp ( [TabName] VARCHAR(255), [PersonID] VARCHAR(255), [FirstName] VARCHAR(255), [Gender] VARCHAR(255), [BenefitType] VARCHAR(255), ...
TMilliman's user avatar
1 vote
2 answers
143 views

Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?

My problem I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following MERGE INTO table USING (SELECT ? AS Search_Col) AS SRC ON table....
Martin Guth's user avatar
1 vote
1 answer
1k views

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?

Is there a scenario where it's faster for synchronizing the data from TableA to TableB by using a MERGE query as opposed to dropping TableB and doing a SELECT * INTO TableB FROM TableA?
J.D.'s user avatar
  • 37.5k
3 votes
1 answer
691 views

Concatenate a string value from table A to a text field in table B, in a scalable way

On a daily basis, I am adding values from the following table (table1): Id Name Value 1 'Name 1' 789 2 'Name 2' 789 3 'Name 3' 789 To the text field (Values) in the following ...
Scarabas's user avatar
1 vote
1 answer
101 views

how to approach user acceptance testing for merged constituent records in a CRM system?

I've been Googling to read up and understand what UATs are and how to conduct them, but I'm not quite understanding how to apply it in this scenario. My acceptance criteria would be that all ...
SorryEh's user avatar
  • 125
0 votes
1 answer
453 views

Microsoft SQL Server MERGE deadlock issue

I am using Microsoft SQL Server version: Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows ...
JangWei's user avatar
4 votes
2 answers
1k views

Ms sql MERGE INTO locks whole table for updates

I have a table for statistic values, it holds millions of records, which is defined like this: CREATE TABLE [dbo].[Statistic] ( [Id] [INT] IDENTITY(1, 1) NOT NULL , [EntityId] [INT] NULL , [...
xumix's user avatar
  • 143
0 votes
2 answers
938 views

Better ways to update columns of a table than using Merge Statement

I have a table with this structure Table1(Cust_No Varchar2(50), First_Name Varchar2(50), Last_Name Varchar2(50), Error_Code Number(1) , Error_Desc Varchar2(50)) ...
Pantea's user avatar
  • 1,412
1 vote
1 answer
9k views

SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses

I want SQL Server to throw an error at me if my SOURCE table contains duplicates as per the ON clause in every case, including INSERT, but it only does for the UPDATE and DELETE cases. Why doesn't it? ...
Elaskanator's user avatar
0 votes
1 answer
297 views

SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014

I have 2 databases that contain nearly identical data. The first has compatibility level 100 and is restored on a sql2012 instance. The second has compatibility level 110 and is restored on a sql2014 ...
Steven Hsu's user avatar
0 votes
1 answer
143 views

T-SQL query to merge all rows into one row [duplicate]

I have a T-SQL query, which pulls details from several locations (Something like below): IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable; CREATE table #mytable ([Locno] varchar(...
chhibbz's user avatar
  • 101
1 vote
0 answers
449 views

SQL Server 2017 Merge Not Running Triggers

Everywhere I am reading, when a merge statement happens, it will fire the triggers (INSERT, UPDATE, DELETE) in no particular order. I have a basic merge: MERGE dbo.Users WITH(HOLDLOCK) as tgt USING #...
Brian Mains's user avatar
3 votes
1 answer
470 views

SSIS Merge Join: Delete does not work

I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere). Merge Join: Inserting and Updating works fine,...
wp78de's user avatar
  • 283
1 vote
0 answers
23 views

Filtered Merge Statement [duplicate]

This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated: I created a web form, to a stored procedure, that submits userID, deptID, and a ...
Wes's user avatar
  • 1,126
-1 votes
1 answer
790 views

LEFT OUTER JOIN fails to find matching row

This one has me about beat. The following is code within a stored procedure that, if run repeatedly, continues to 1) fail to identify a matching row and 2)insert a new identical row. The values and ...
Steve_Malcolm's user avatar
2 votes
1 answer
272 views

Merge/Intersection between table and dataset - How to achieve?

Consider the following table: Id Hash ----------- ---------------------------------- 1 0x31F777F0804D301936411E3ECD760859 2 0xD64A593F3E9ACC972158D522A4289EA0 (Id is an ...
James's user avatar
  • 133
0 votes
1 answer
238 views

Using a DB2 merge with conditionals for matches?

Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work. The logic I'm looking for is this: If there's a match AND the ...
Whisou138's user avatar
  • 103