---
title: "Steps to Insert Modified Rows Keeping Orignal Data Intact : Databricks SQL Simplified"
description: "The content outlines a three-step process using Databricks SQL and PySpark to update employee salary records. It involves creating target and lookup tables, inserting data, and forming a temporary table to hold modified rows. Finally, it implements a script to dynamically insert only the updated columns back into the target table."
url: https://srinimf.com/2025/02/01/steps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified/
date: 2025-02-01
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/target_lookup.png?fit=2240%2C1260&ssl=1
categories: ["databricks"]
tags: ["Target and Lookup Tables"]
type: post
lang: en
---

# Steps to Insert Modified Rows Keeping Orignal Data Intact : Databricks SQL Simplified

Modified fields will be written into a temporary table compared to a lookup table. Later, by excluding the match flag, the modified columns can be inserted into the target tables without affecting the original rows.

## **Step 1️⃣: Databricks SQL – Create & Populate Tables**

### **1.1 Create Target Table (`employees_target`1)**

```
CREATE TABLE IF NOT EXISTS employees_target1 (
    emp_id INT,
    name STRING,
    department STRING,
    salary DOUBLE
);




1.2 Insert Data into Target Table



INSERT INTO employees_target1 VALUES 
(1, 'Alice', 'HR', 60000),   -- Will be updated (exists in lookup)
(2, 'Bob', 'IT', 70000),     -- No change
(3, 'Charlie', 'Finance', 80000),  -- No change
(4, 'David', 'IT', 75000),   -- No change
(5, 'Eve', 'Marketing', 65000), -- Will be updated (exists in lookup)
(6, 'Frank', 'Sales', 72000); -- No change








1.3 Create Lookup Table (employees_lookup2)



CREATE TABLE IF NOT EXISTS employees_lookup2 (
    emp_id INT,
    salary_new DOUBLE -- Reference salary for comparison
);




1.4 Insert Data into Lookup Table



INSERT INTO employees_lookup2 VALUES 
(1, 80000),  -- Alice: Compare replace with 80K (should be updated)
(5, 90000);  -- Eve: Compare replace with 90K (should be updated)





🔹 This lookup table provides reference emp_id values for comparison.
🔹 If emp_id matches it replaces with new salary .




Step 2️⃣: Create a Temp Table with Only Updated Columns



🔹 This Temp table selects only the modified employees and their updated columns.
🔹 It excludes unchanged columns like name and department.



CREATE OR REPLACE TABLE employees_temp_table1 AS 
SELECT 
    t.emp_id as match,
    COALESCE(l.salary_new, t.salary) AS salary  -- Use compare_salary from lookup
FROM employees_target1 t
FULL OUTER JOIN employees_lookup2 l
ON t.emp_id = l.emp_id
WHERE l.emp_id IS NOT NULL;  -- Only include rows where lookup exists







Step 3️⃣: PySpark Script – Insert Only Modified Rows into Target




Load employees_temp_table (modified rows only).



Dynamically get column names using DESCRIBE.



Exclude the match column from inserts.



Insert only updated columns into employees_target1.




%python
from pyspark.sql import SparkSession

# Initialize Spark session
spark = SparkSession.builder.appName("Databricks-Merge").getOrCreate()

# Retrieve the column names from target table using DESCRIBE TABLE
target_columns_df = spark.sql(f"DESCRIBE TABLE employees_target1")
target_columns = [row['col_name'] for row in target_columns_df.collect() if row['col_name'] != '']

# Exclude the match column from temp table using DESCRIBE TABLE
temp_columns_df = spark.sql(f"DESCRIBE TABLE employees_temp_table1")
temp_columns = [row['col_name'] for row in temp_columns_df.collect() if row['col_name'] != '' and row['col_name'] != 'match']

# Construct the column list for the INSERT statement
columns_str = ', '.join(target_columns)

# Construct the values list for SELECT statement
values_str = ', '.join([f"temp.{col}" if col in temp_columns else f"tgt.{col}" for col in target_columns])

# Construct the dynamic SQL query
dynamic_sql_query = f"""
INSERT INTO employees_target1 ({columns_str})
SELECT {values_str}
FROM (
    SELECT DISTINCT match, {', '.join(temp_columns)}
    FROM employees_temp_table1
) temp
JOIN employees_target1 tgt ON temp.match = tgt.emp_id
WHERE temp.match IS NOT NULL;
"""

# Execute the dynamic SQL query
spark.sql(dynamic_sql_query)

print("Modified rows inserted successfully into employees_target1.")
```

Related[Complete Guide to MERGE INTO in Databricks](https://srinimf.com/2025/01/31/complete-guide-to-merge-into-in-databricks/)

---

## **📌 Final Output Data**

#### **Before Update (`employees_target`1)**

![Before Insert Target Table data.](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/image-3.png?resize=408%2C148&ssl=1)

#### **After Running the Script (`employees_target`1)**

![Target table output](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/02/image-2.png?resize=439%2C194&ssl=1)

---

## **✨ Summary**

✔ **Step 1:** Created `employees_target`1 and `employees_lookup`2 with reference salaries for comparison.
✔ **Step 2:** Create `TEMP TABLE (employees_temp_TABLE1)` one that contains **only updated columns**.
✔ **Step 3:** **Python dynamically detects modified rows** and inserts them into `employees_target`1.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2025%2F02%2F01%2Fsteps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2025%2F02%2Ftarget_lookup.png%3Ffit%3D1200%252C675%26ssl%3D1&description=Steps%20to%20Insert%20Modified%20Rows%20Keeping%20Orignal%20Data%20Intact%20%3A%20Databricks%20SQL%20Simplified)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2025/02/01/steps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2025/02/01/steps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2025/02/01/steps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2025/02/01/steps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Steps%20to%20Insert%20Modified%20Rows%20Keeping%20Orignal%20Data%20Intact%20%3A%20Databricks%20SQL%20Simplified&body=https%3A%2F%2Fsrinimf.com%2F2025%2F02%2F01%2Fsteps-to-insert-modified-rows-keeping-orignal-data-intact-databricks-sql-simplified%2F&share=email)
-

### Like this:

Like Loading…