---
title: "Busting SQL Migration Myths: How New SQL Features Make Lift-and-Shift to Lakehouse Easier"
description: "The post addresses the procedural core of warehouse-to-lakehouse migrations: legacy stored procedures that use temporary tables, cursors, control flow, multi-table updates, and transaction handling while remaining maintained by SQL teams. Using a composite daily-orders procedure based on an Oracle migration, it translates rather than rewrites the logic on Databricks, covering procedure registration in Unity Catalog, session-scoped temporary tables, cursor syntax, handlers, conditional scripting, and atomic transactions. Databricks SQL scripting supports cursors from Runtime 18.1, and BEGIN ATOMIC provides automatic commit or rollback plus row-level conflict detection; catalogManaged tables are required inside atomic blocks. The post says this mechanical translation can cut migration timelines by 50–75% for complex procedures, while preserving business logic and allowing the SQL team to maintain it, though the procedure still needs validation in a migration project."
---

# Busting SQL Migration Myths: How New SQL Features Make Lift-and-Shift to Lakehouse Easier

[Databricks](https://yomu.fyi/company/databricks) · Abhishek Dey, Laurent Léturgez · Aug 20, 2026

**Type:** Tutorial

## Summary

The post addresses the procedural core of warehouse-to-lakehouse migrations: legacy stored procedures that use temporary tables, cursors, control flow, multi-table updates, and transaction handling while remaining maintained by SQL teams. Using a composite daily-orders procedure based on an Oracle migration, it translates rather than rewrites the logic on Databricks, covering procedure registration in Unity Catalog, session-scoped temporary tables, cursor syntax, handlers, conditional scripting, and atomic transactions. Databricks SQL scripting supports cursors from Runtime 18.1, and BEGIN ATOMIC provides automatic commit or rollback plus row-level conflict detection; catalogManaged tables are required inside atomic blocks. The post says this mechanical translation can cut migration timelines by 50–75% for complex procedures, while preserving business logic and allowing the SQL team to maintain it, though the procedure still needs validation in a migration project.

## Context

Warehouse migrations are often delayed by procedural SQL rather than data movement itself. The procedures may contain nested cursors, temporary tables, control flow, multi-table updates, and transaction handling, while enterprise teams remain dependent on SQL skills and existing business logic.

## Approach / What changed

The procedure is translated incrementally to Databricks SQL instead of being rewritten in Python and Spark. The approach maps legacy exception handling, temporary tables, cursors, control flow, and transactions to Databricks SQL scripting, registers the procedure in Unity Catalog, and uses BEGIN ATOMIC for commit, rollback, and row-level conflict detection.

## Takeaways

- Databricks SQL scripting supports native cursors, including OPEN, FETCH, and CLOSE, from Runtime 18.1; %NOTFOUND maps to a CONTINUE HANDLER FOR NOT FOUND, while loop labels and LEAVE replace EXIT WHEN.
- CREATE TEMP TABLE provides the session-scoped replacement for legacy temporary tables. CREATE OR REPLACE TEMP TABLE is not supported, so a table must be dropped first when rerunning in the same session.
- BEGIN ATOMIC commits on success and rolls back on failure, with row-level conflict detection for concurrent writes. Tables defined inside the block need the catalogManaged table feature enabled.

**Tags:** [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Migrations](https://yomu.fyi/topic/migration)

- Source: [Databricks](https://www.databricks.com/blog/busting-sql-migration-myths-how-new-sql-features-make-lift-and-shift-lakehouse-easier)
- Source URL: https://www.databricks.com/blog/busting-sql-migration-myths-how-new-sql-features-make-lift-and-shift-lakehouse-easier
- Ingested by Yomu: 2026-08-30T16:50:56.459Z

[Read original post](https://www.databricks.com/blog/busting-sql-migration-myths-how-new-sql-features-make-lift-and-shift-lakehouse-easier)