Questions tagged [merge]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
33 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? ...
0
votes
1answer
88 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 ...
0
votes
1answer
42 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(...
1
vote
0answers
36 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
vote
0answers
26 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 #...
2
votes
1answer
49 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,...
0
votes
0answers
27 views

Synchronizing for reporting, SSIS job vs Transactional replication or other options?

We're faced with a fun challenge. A server has 60+ databases to segment data due to privacy and security. We have 7 tables in each of these databases that need to be merged into a master dataset for ...
0
votes
0answers
31 views

Informix - where clause with merge into

I am using Informix Dynamic Server Version 12.10.FC9W1X2. I need to update a table with fields from a different table. I am attempting to use the MERGE INTO statement, but I am not able to use a ...
1
vote
0answers
18 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
votes
1answer
133 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 ...
2
votes
1answer
155 views

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

Consider the following table: Id Hash ----------- ---------------------------------- 1 0x31F777F0804D301936411E3ECD760859 2 0xD64A593F3E9ACC972158D522A4289EA0 (Id is an ...
0
votes
1answer
47 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 ...
0
votes
1answer
113 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.. ...
8
votes
1answer
656 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 ...
4
votes
3answers
703 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 ...
0
votes
3answers
2k 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 ...
2
votes
2answers
644 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 ...
1
vote
0answers
278 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 ...
0
votes
1answer
2k 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
1answer
382 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 ...
0
votes
1answer
1k 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`...
1
vote
1answer
3k 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, ...
-1
votes
2answers
55 views

Merging table output into a column in another table

I have an interesting problem to work in SQL that I really need help with. What I need to do is to take a table, count the number of repeats/duplicates in a column, add those up and then spit the ...
-1
votes
1answer
17 views

Merging output in table by matching records

I have an interesting problem to work in SQL that I really needenter code here help with. What I need to do is to take a table, count the number of repeats/duplicates in a column, add those up and ...
0
votes
2answers
54 views

Schema Changes Are Not Populating to The Subscribers

One of my publications was dropped mistakenly. I restored it from the latest backup but now schema changes are not replicated to the subscribers. What can I do to re-initialize/fix the publication?
1
vote
1answer
304 views

How can I get identity-like values in a MERGE INSERT statement

I would like to merge a table bar into another table foo, and while doing so I have to assign a unique value to a column messagenr. This is my attempt to do so (sql-server): MERGE foo AS target ...
0
votes
1answer
44 views

Is updating many columns in a MERGE statement bad for performance?

I have a merge statement that either needs to insert about 65 rows worth of data, or update rows. At the moment I'm updating all columns when a match is found. But I don't need to. Would there be a ...
1
vote
2answers
202 views

“Query processor ran out of internal resources and could not produce a query plan” for MERGE with 2,600 records and one key field!

I'm writing a Visual Studio SQL database project for an existing database. I'm generating simple merge scripts per table for standing data, existing data so on and so forth. It's been relatively ...
0
votes
1answer
10k views

SQL Server : Merge and update multiple rows across columns

I am trying to consolidate data in one table and merge it into another. The data in the source destination table is as follows : name |dob |city |occupation --...
1
vote
1answer
2k views

SQL Server - MERGE join condition with NULLable Columns

I am trying to merge two of my tables (identical definition). MERGE INTO xx.dbo.acc_s AS Target USING yy.dbo.acc_s AS Source ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND ...
0
votes
0answers
505 views

Merge saying duplicate rows, but can't find any

When I run the following: merge into LIBRARYA.TABLEA mergeTarget using LIBRARYB.TABLEB mergeUsing on mergeTarget.COLA1 = mergeUsing.COLB1 and mergeTarget.COLA2 = mergeUsing.COLB2 when ...
8
votes
1answer
2k views

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, ...
1
vote
0answers
313 views

How to find MariaDb/Mysql structure differences

I have two similar databases with many tables, in one of these there was some alter commands maybe changing data types or adding some columns. Exists a free analyzer/merging tool to check their ...
0
votes
2answers
4k views

MySQL merge two tables of different database to single database [closed]

Friends, i have database named as DB1 with a table called TB1A and another database as DB2 with a table TB2A. TB1A having 5 columns and TB2A having 8 columns, All the columns available in TB1A is ...
0
votes
2answers
3k views

Linked Server : Syncing two tables on two SQL Servers

Suppose these database structure : --On main server(server) TicketRequest (id, date, count, start, end/*,and some other columns*/) Ticket (id, reqId, blockedDate, usedDate, userId/*,and some other ...
-2
votes
1answer
415 views

Postgresql select columns based on json column [closed]

Thanks in advance. table-1 | ID | name | json | | 1 | jack | {color: 'red', size: 'large' } | table-2 | ID | color | size | qty | | 1 | black | small| 10 | If json is not null result | ID | ...
0
votes
1answer
2k views

Insert And Update Statement With Merge

I am attempting to execute the below syntax and I get two errors A MERGE statement must be terminated by a semi-colon (;). Incorrect syntax near '='. Now if I comment out the last line of code ...
-1
votes
2answers
1k views

Using an Aggregate In Merge Update Statement

I am attempting to run a COUNT() statement in an update statement. However my syntax presents this error Msg 157, Level 15, State 1, Line 6<br> An aggregate may not appear in the set list of ...
0
votes
1answer
73 views

Merge 2 pairs of tables from 2 databases. Beginner

I have two MySql databases DB1 and DB2. Each of them has tables tbl1 and tbl2. These two tables have the same primary key (Column1). Tables in DB1 have N rows. I want to create new database and table, ...
0
votes
1answer
40 views

Updating a set of rows in a table with values from a different set of rows. Both sets are from the same table

I want to update a set of rows in a table with data from a different set of rows from the same table. Ex: I want to update Customer Invoice Amounts from year 2018 with Customer Invoice Amounts from ...
0
votes
1answer
1k views

Merge multiple InnoDB tables in MySQL

I have a big InnoDB table (7 million records) and I need to speed up the insert/update operations. For instance, this is what the table looks like: CREATE TABLE product ( year int not null, ...
-1
votes
1answer
1k views

Does Merge take Order by into account when updating

I have to merge a subquery result into table A. Here's the basic merge statement: MERGE INTO A USING (SELECT CUSTOMER_ID,LAST_LOGIN_DATE) B ON A.CUSTOMER_ID=B.CUSTOMER_ID WHEN MATCHED THEN UPDATE ...
1
vote
1answer
128 views

When I have 6 cores and they do a table scan and merge afterwards, does every core scan the whole table?

I remember that I once had a missing index on a table and one query was taking ages. In the execution plan I saw that a table scan and a merge was done. Statistics from IO showed me that every core ...
3
votes
1answer
2k views

Merge Failed when update same rows

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row ...
4
votes
3answers
365 views

Best way to merge tightly coupled SQL Server Instances

I´m looking for some recommendations or best practice for the following scenario: I need to merge several SQL Server 2014 instances into one (we are switching from many instances to a single ...
2
votes
1answer
145 views

Blocking upsert procedure

Setup I have a simple table: CREATE TABLE [dbo].[StringData]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [DCStringID] [bigint] NOT NULL, [TimeStamp] [datetime] NOT NULL, [Hour] AS (...
12
votes
2answers
3k views

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 ...
5
votes
1answer
189 views

Writing and reading date ranges for a million items

I have a table with one row per day for about a million slowly changing items. The items are looked up by Item, Date to see if they're present in the table. This is going somewhat slowly for lookups ...
0
votes
1answer
61 views

Access 2013: Merging the same table-field with different data

I have six tables with identical fields. the group was tasked to input the data with the same fields and I need to merge all into one dataset--- table and form.
1
vote
1answer
84 views

Merge performs always additional INSERTS

I am facing the followign issue, my MERGE statements based on data (views) that do not deliver primary keys or any unique keys, always just ADD additional INSERTS on every execution to the target ...