Questions tagged [merge]
A SQL statement that can conditionally insert, update, or delete target rows.
164
questions
25
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 ...
- 353
5
votes
1
answer
297
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 ...
- 53
0
votes
0
answers
22
views
Large log backup files on subscriber side of merge replication
I have a merge replication setup in SQL Server 2019 (Full recovery model) that is working great with no problems in use.
The publisher and the subscriber both have weekly full backups, and hourly log ...
- 101
1
vote
1
answer
211
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 ...
- 341
5
votes
1
answer
161
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 ...
- 1,382
0
votes
1
answer
30
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 ...
- 11
0
votes
1
answer
84
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 ...
- 101
11
votes
3
answers
564
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 ...
- 213
0
votes
1
answer
100
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 ...
- 401
0
votes
1
answer
69
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 ...
0
votes
1
answer
434
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 ...
- 401
0
votes
0
answers
180
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 ...
- 1,199
1
vote
1
answer
439
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,
...
- 1,292
0
votes
1
answer
943
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 ...
- 1
0
votes
3
answers
1k
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 ...
- 448
0
votes
1
answer
305
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 ...
- 1
0
votes
1
answer
721
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 ...
- 3
0
votes
1
answer
309
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....
- 531
0
votes
1
answer
75
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 ...
0
votes
2
answers
3k
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 ...
- 378
1
vote
0
answers
25
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....
- 111
1
vote
0
answers
160
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 ...
0
votes
1
answer
19
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),
...
- 75
1
vote
2
answers
73
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....
- 635
1
vote
1
answer
405
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?
- 24.6k
3
votes
1
answer
152
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 ...
- 31
1
vote
1
answer
35
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 ...
- 125
0
votes
1
answer
312
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 ...
- 1
4
votes
2
answers
808
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
, [...
- 143
0
votes
2
answers
51
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))
...
- 1,292
1
vote
1
answer
5k
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?
...
- 681
0
votes
1
answer
153
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 ...
- 41
0
votes
1
answer
92
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(...
- 101
0
votes
0
answers
111
views
How to capture 'Deleted' and 'Updated' record after Merge in PL/SQL.TSQL 'Output' equivalent in PL/SQL
hope you're doing well.
imagine I have a source and destination table like:
Source(Column1 int , column2 varchar2(50))
Destination (Column1 int , column2 varchar2(50))
I want to merge these two ...
- 1,292
1
vote
0
answers
322
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 #...
- 587
3
votes
1
answer
242
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,...
- 283
1
vote
0
answers
22
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 ...
- 1,136
-1
votes
1
answer
469
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 ...
- 135
2
votes
1
answer
188
views
Merge/Intersection between table and dataset - How to achieve?
Consider the following table:
Id Hash
----------- ----------------------------------
1 0x31F777F0804D301936411E3ECD760859
2 0xD64A593F3E9ACC972158D522A4289EA0
(Id is an ...
- 133
0
votes
1
answer
117
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 ...
- 103
0
votes
1
answer
2k
views
WHEN MATCHED clause fails if there's a NULL in the ON columns [duplicate]
I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns:
MERGE PEPS AS peps
USING (Lots of Cols..
...
user151179
8
votes
1
answer
1k
views
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 ...
- 255
4
votes
3
answers
3k
views
Does MERGE prevent deadlocks and server blocking?
I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while ...
- 165
0
votes
3
answers
9k
views
Insert Values Into Table, Putting Duplicates In Another Table
So I posted this question yesterday. Some of the responses I got were helpful, however it seems my issue is a bit more complex than I originally thought.
After doing some looking the reason I was ...
- 371
2
votes
2
answers
1k
views
Help With Merge Statement
I am wanting to compare the Distinct sn from HMI_Temp to sn in my table HMI -> if the sn exists, I want to update the values - got this syntax CHECK! - if the sn does NOT exist, I want to insert the ...
- 940
1
vote
0
answers
503
views
Is it possible to use target column value on insert in SQL Server merge 'not matched' block?
When working with a MERGE statement in SQL Server to use the updated state of a temp table to later modify records across multiple tables in a db schema, I am running in an issue when executing the ...
- 111
0
votes
1
answer
8k
views
How can I merge data from an Excel file into an access database with multiple tables? [closed]
I have an access database with many internal tables.
Each table is a category of electronic components (resistor, capacitor etc)
In all tables, there is a manufacturer part number and a currently ...
4
votes
1
answer
859
views
Breaking up insert statements with GO [closed]
I'm trying to build off of this answer How do I run a large script with many inserts without running out of memory?
and break up a query into smaller ones with BEGIN TRANSACTION and then GO's
...
- 173
0
votes
1
answer
3k
views
Merge data with UNION ALL and INNER JOIN in MySQL
I try merge a data to one row. I have query:
(select t.`id`,
t.`coins_id`,
t.`first_coin`,
t.`second_coin`,
t.`price` as `price_old`,
null as `price_young`...
- 105
4
votes
1
answer
10k
views
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, ...
- 56.2k