Stack Overflow
Stack Overflow

Questions tagged [sql-merge]

`SQL MERGE` statement allows us to insert new rows into table and update existing rows depending on given condition. Use this tag in addition to [tag:sql] to make question better categorized.

SQL MERGE statement allows us to insert new rows into table and update existing rows depending on given condition.

Examples of using MERGE statement:

Use this tag in addition to to make question better categorized.

324 questions
9
votes
3 answers

MERGE table, do nothing when matched

I have a table DOMAINS in 2 different schemas with columns ID, NAME,CODE,DESCRIPTION. For any NAME exist in new schema, it should use existing ID without any merge; for those new NAME records, it should insert with ID from old schema. MERGE INTO…
user2102665
  • 429
  • 2
  • 11
  • 26
8
votes
3 answers

Is there a "Script table as - MERGE" function somewhere?

In SSMS 2008 R2, when I right click on a table I see "Script Table as" then options for Insert and Update. But what about Merge? Merge is really just the two of these together. Is there any tool I can get that will add that option? (So I can…
Vaccano
  • 78,325
  • 149
  • 468
  • 850
8
votes
1 answer

MERGE using a rowtype variable in PL/SQL on Oracle?

With a variable bar of the type foo%ROWTYPE I can do both INSERT and UPDATE in PL/SQL: INSERT INTO foo VALUES bar; UPDATE foo SET ROW = bar WHERE id = bar.id; But how do I do a MERGE? The following approach generates the error message below: MERGE…
Anders
  • 8,307
  • 9
  • 56
  • 88
6
votes
2 answers

How can I auto increment the primary key in SQL Server 2016 merge insert without sequences?

I am writing a query to import data from one table to a new table. I need to insert records that do not exist in the new table, and update records that do exist. I am trying to use a MERGE "upsert" method. I have some unique problems due to the…
Winch
  • 117
  • 1
  • 9
6
votes
1 answer

Is Merge and Merge join same in SQL Server?

What is the difference between Merge and a Merge Join in SQL Server?
VVN
  • 501
  • 1
  • 9
  • 21
6
votes
1 answer

Stored Procedure returns schema version change error when run from SSIS, but not when run directly

I have a SQL Server stored procedure that executes correctly every time when run manually with EXEC, but when it runs as part of an SSIS package, it fails with an error like this: Executing the query "EXECUTE (ProcName) " failed with the following…
Ben Wyatt
  • 399
  • 4
  • 14
5
votes
3 answers

SQL Server: how to delete rows with matching ID that were not affected by MERGE?

I have a table with an auto incrementing id and a unique constraint across two columns, keycol1 and keycol2. Suppose the table has this data: H| (id, keycol1, keycol2, col1, col2) | (1, 'A', 'B', 'A', 'E' ) | (2, 'A', 'C', 'J', …
Menasheh
  • 3,560
  • 3
  • 33
  • 48
5
votes
1 answer

Using Oracle MERGE on same table based on condition

I have created an Oracle sequence as below: CREATE SEQUENCE TASK_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; I have a database table TASK as below: TASK_ID nextval from TASK_ID_SEQ TASK_DATE SYSDATE TASK_TYPE I…
Dr. Debasish Jana
  • 6,980
  • 4
  • 30
  • 69
5
votes
1 answer

Unkillable Oracle session waiting on "SQL*Net message from client" event

On Oracle 11gR2, I've recently encountered a very interesting situation involving a blocked (but idle!) MERGE statement that hangs on a "SQL*Net message from client" event, causing subsequent, concurrently executed MERGE statements to block on the…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
2 answers

Update and Insert When Condition is Matched in TSQL-Merge

I have been trying to Write a Stored Procedure where i can perform UpSert using Merge with the Following Condition If Record is Present then change EndDate of Target to Yesterday's day i.e., Present Day - 1 If Record is not Present then Insert New…
Amar
  • 407
  • 1
  • 5
  • 24
4
votes
2 answers

SQL Server: Merge statement compilation error

I have used SQL Server MERGE statement in my stored procedure and when compiling the stored procedure I'm getting this error: The object reference [dbo].[Student].[ID] differs only by case from the object definition [dbo].[Student].[Id]. My…
Mou
  • 15,673
  • 43
  • 156
  • 275
4
votes
3 answers

Migrating an Oracle MERGE statement to a PostgreSQL UPSERT statement

Can you please help me converting the following Oracle MERGE statement into a valid UPSERT statement for use in a PostgreSQL 9.3 database? MERGE INTO my_table a USING (SELECT v_c1 key, v_c2 AS pkey, v_c3 AS…
gpa
  • 2,411
  • 6
  • 38
  • 68
3
votes
1 answer

Merge statement and after triggers on target table

I have two after triggers on target table (one for insert and one for update). Now if I execute merge on the target table, the triggers are executed only once. Although the merge statement executes around 300 updates, and 200 inserts. I checked it…
pierre
  • 31
  • 1
  • 2
3
votes
1 answer

Why merge-delete-trigger causes ORA-30926: unable to get a stable set of rows in the source tables?

Prepare the schema to reproduce the issue (on db<>fiddle): create table t (id int, val int, modified timestamp default systimestamp) / create or replace trigger trigg_on_t before update on t for each row enable begin :new.modified :=…
0xdb
  • 3,539
  • 1
  • 21
  • 37
3
votes
1 answer

Lock level on MERGE statement

Does anyone know what is the lock level (row, table) on PostgreSQL MERGE command? I tried to dig deep on the documentation but I couldn't find anything that could clarify my mind. If anyone have a clue and would be kind to share, that would be very…
wmrodrigues
  • 483
  • 1
  • 3
  • 10
1
2
3
…
21 22