Difference between Append and Merge data

Kiran Kumar

1y1kSQL Server

What is the difference between Appnend and Merge in SQL server database?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaish Mathews1y ago

Accepted answer

I collected a tabular represenation of this from another source.

Here’s a tabular representation of the differences between Append and Merge in a SQL Server database:

Feature	Append	Merge
Definition	Adds new rows to a table without modifying existing data.	Combines datasets by inserting, updating, or deleting rows in the target table based on conditions.
Use Case	Used when you need to add additional rows from one table to another.	Used when you need to synchronize or reconcile differences between two datasets.
Effect on Data	Only inserts new rows into the table.	Performs INSERT, UPDATE, or DELETE operations depending on specified conditions.
Primary Statement	`INSERT INTO ... SELECT ...`	`MERGE INTO ... USING ... ON ... WHEN MATCHED ... WHEN NOT MATCHED ...`
Existing Data	Does not affect existing rows in the target table.	Can modify (update/delete) existing rows and add new rows.
Complexity	Relatively simple operation.	More complex, with multiple conditions and actions.
Performance	Typically faster as it involves only inserting data.	Can be slower due to conditional logic and multiple potential operations (INSERT, UPDATE, DELETE).
Conflict Handling	No handling for conflicts, assumes no duplicates or overlap in target data.	Can handle conflicts using `WHEN MATCHED` and `WHEN NOT MATCHED` clauses.
Typical Scenario	Appending historical data to a log or archive table.	Keeping a target table (e.g., a staging or final table) in sync with a source dataset.

Append Example

This example appends data from SourceTable to TargetTable.

-- APPEND operation: Insert all rows from SourceTable into TargetTable
INSERT INTO TargetTable (Column1, Column2, Column3)
SELECT Column1, Column2, Column3
FROM SourceTable;

Merge Example

This example synchronizes data between SourceTable and TargetTable. Rows are:

Updated if they match.
Inserted if they don't exist.
Deleted if they are missing in the source.

-- MERGE operation: Synchronize SourceTable with TargetTable
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID  -- Matching condition (e.g., primary key)

WHEN MATCHED THEN
    UPDATE SET 
        Target.Column1 = Source.Column1,
        Target.Column2 = Source.Column2

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Column1, Column2)
    VALUES (Source.ID, Source.Column1, Source.Column2)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Optional: You can output changes for verification
OUTPUT $action, inserted.*, deleted.*;

Amit Mohanty1y ago

In SQL Server, append means adding new rows to the table by using INSERT and does not check or change any data. It is easy and great for adding data.

This operation merges data from a source and target table. It is an advanced operation because it allows conditional actions like INSERT, UPDATE, or DELETE based on a match condition, hence suitable for data synchronization. Use Append for easy data additions and Merge for complicated situations with several operations.

Key Differences:

Feature	Append (`INSERT`)	Merge
Operation Scope	Only adds new rows	Can add, update, or delete rows
Data Check	No check for duplicates	Checks data based on condition
Complexity	Simple syntax	Complex syntax
Flexibility	Limited	Highly flexible
Performance	Fast for bulk inserts	Slower due to conditional checks
Best Use Case	Adding new data	Synchronizing tables or handling conditional updates

You can choose Append (INSERT) for straightforward data additions and Merge when working with more complex data synchronization needs.

Muhammad Imran Ansari1y ago
Hi Kiran,

Yes, SQL Server supports the MERGE statement, which allows you to perform operations like INSERT, UPDATE, and DELETE in a single query by comparing a target table with a source table. It’s commonly used for scenarios like data synchronization.

On your second query regarding append Operation, for appending rows from one table to another, you typically use the INSERT INTO statement.

You cannot use UNION or UNION ALL directly to append rows from one table to another. UNION combines results from multiple queries into a single dataset, which is useful for querying but doesn’t modify the target table.

When using INSERT INTO ... SELECT, the columns selected from the source table must align with the target table's column order, unless you explicitly specify the column names in both the INSERT INTO and SELECT clauses.

Thank you!
Kiran Kumar1y ago
Thank you for all responses

As you mentioned Merge is a keyword in database we can get the feature of merging tables , but do we have this syntax in MS SQL server

Append meaning adding new rows into a table from another tab

Here, I have a question can we user union and union all for this append instead of Insert into table script

Do we need to have column position same as the source table from traget
Muhammad Imran Ansari1y ago
Hi Kiran,

Append and Merge are not explicit SQL commands in SQL Server. However, these are often used in database operations to describe different actions.
Append: This refers to adding data to an existing table without modifying or checking existing rows. A common example is the INSERT command.
```
INSERT INTO TargetTable (Column1, Column2, ....., ColumnN)
SELECT Column1, Column2, ....., ColumnN
FROM SourceTable
```
Merge: This is a combination of INSERT, UPDATE, and DELETE operations into a single statement. A comomn example is the MERGE statement. Here is a simple example of a Merge:

Target Table:

Name

ID Name Age

1 Alice 25

2 Bob 30

Source Table:

ID Name Age

2 Bob Updated 35

3 Charlie 22
```
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name,
               Target.Age = Source.Age
WHEN NOT MATCHED THEN
    INSERT (ID, Name, Age)
    VALUES (Source.ID, Source.Name, Source.Age);
```
Thank you!
Jayraj Chhaya1y ago
Hi

In SQL Server, the terms "Append" and "Merge" refer to different operations for handling data within tables.

Append typically involves adding new rows to an existing table. This can be achieved using the INSERT statement. For example:
```
INSERT INTO TargetTable (Column1, Column2)
VALUES (Value1, Value2);
```
This operation simply adds new records without altering existing ones.

On the other hand, Merge is a more complex operation that allows you to perform insert, update, or delete actions based on the comparison of two datasets. The MERGE statement is particularly useful for synchronizing two tables.
```
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED THEN
    UPDATE SET target.Column1 = source.Column1
WHEN NOT MATCHED THEN
    INSERT (Column1, Column2) VALUES (source.Column1, source.Column2);
```
While Append is about adding new data, Merge provides a comprehensive way to synchronize and manage data between two tables.

ID	Name	Age
1	Alice	25
2	Bob	30

ID	Name	Age
2	Bob Updated	35
3	Charlie	22