SQL Tran Docs
  • Overview
    • About
    • Getting started
    • Object lifecycle
    • Why is there no AI inside?
    • Performance considerations
    • Security considerations
    • Assessment
    • Unlocking projects
    • Interface walk-through
    • Translation scenarios
    • Prerequisites for Azure Marketplace deployment
  • Emulation scenarios
    • Emulation in SQL Tran
    • SQL Server to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Cursors
        • Basic cursor loop
        • Cursor types
        • Fetch direction modes
        • Cursors in control flow
        • Nested cursors
        • Data modification cursors
        • Multiple cursors
        • Subqueries and filtering
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Computed columns
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Sequences
      • Unsupported system features
    • Synapse Analytics to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Materialized views
      • Create table as select (CTAS)
      • Unsupported system features
    • SQL Server to Synapse Analytics
    • Oracle to PostgreSQL
  • Project wizard
    • Source database
    • Target database
    • Wrapping up
  • Projects
    • Project list
    • Overview
    • Workspace
    • Reports
    • Tests
    • Scratch pad
    • Settings
      • Project name
      • Mapping
      • Database connections
    • Navigation
    • Object complexity
    • Static analysis
    • Translation errors
    • Exporting and importing projects
  • Workspace
    • Object tree
    • Data lineage
    • Code
    • Actions
      • Overriding source
      • Overriding target
      • Ignoring objects
  • Tests
    • Workflow
    • Configure SQL Tran
    • Connecting to databases
      • Fabric Warehouse
      • Synapse Dedicated SQL Pool
      • Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server
    • Tables
    • Views
    • Procedures
    • Functions
    • Triggers
    • Performance tests
  • Scripter
    • About
    • Supported databases
    • SQL Server
    • Azure SQL
    • Synapse Dedicated Pool
    • Oracle
    • PostgreSQL
    • MySQL
  • Microsoft training - SQL Tran
    • Introduction
    • Licensing plans
    • Deploying and activating
    • Creating a new project
    • Exploring the Overview screen
    • Inside the Workspace screen
    • Code translation and emulations
      • Data type emulation
      • Case sensitivity emulation
      • Named parameters emulation
      • Result set limiting emulation
      • Merge statements emulation
      • Identity column emulation
      • Default values emulation
      • Detection of unsupported features
Powered by GitBook
On this page
  • Code example
  • Training video example
  1. Microsoft training - SQL Tran
  2. Code translation and emulations

Default values emulation

PreviousIdentity column emulationNextDetection of unsupported features

Last updated 7 hours ago

In Synapse Analytics, columns can have default values defined in the table schema, allowing INSERT operations to omit those columns and still generate meaningful values automatically. Common defaults include timestamps, status flags, or unique identifiers.

However, Microsoft Fabric Warehouse does not support column-level default constraints — any default behavior must be handled explicitly in application logic.

SQL Tran addresses this by removing unsupported default definitions from table schemas and rewriting INSERT statements to explicitly supply the appropriate default values.

This ensures that data insertion behavior remains consistent without requiring manual code refactoring, preserving the original logic even when migrating large and complex schemas.

(For more information, see the following emulation reference page: )


Code example

Synapse Analytics:

CREATE PROCEDURE [dbo].[TestDefaultValues]
AS
BEGIN
	CREATE TABLE ExampleTable (
		ID uniqueidentifier DEFAULT NEWID() ROWGUIDCOL,
		Name varchar(50) NOT NULL,
		Status varchar(20) DEFAULT 'Active', 
		CreatedAt DATETIME2(6) DEFAULT GETDATE(),
	);

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (NEWID(), 'Alice', 'Inactive', SYSUTCDATETIME());

	INSERT INTO ExampleTable (Name)
	VALUES ('Bob');

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (DEFAULT, 'Charlie', DEFAULT, DEFAULT);

	INSERT INTO ExampleTable DEFAULT VALUES;
END;

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE [dbo].[TestDefaultValues]
AS
BEGIN
	CREATE TABLE ExampleTable (
		ID uniqueidentifier /* SQLTRAN FIX: DEFAULT NEWID() */ /* SQLTRAN FIX: ROWGUIDCOL */,
		Name varchar(50) NOT NULL,
		Status varchar(20) /* SQLTRAN FIX: DEFAULT 'Active' */, 
		CreatedAt DATETIME2(6) /* SQLTRAN FIX: DATETIME2(6) */ /* SQLTRAN FIX: DEFAULT GETDATE() */,
	);

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (NEWID(), 'Alice', 'Inactive', SYSUTCDATETIME());

	
INSERT
	INTO ExampleTable ([ID], Name, [Status], [CreatedAt])
	VALUES (NEWID(), 'Bob', 'Active', GETDATE());
 /* SQLTRAN FIX: INSERT INTO ExampleTable (Name)
	VALUES ('Bob'); */

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (DEFAULT, 'Charlie', DEFAULT, DEFAULT);

	
INSERT
	INTO ExampleTable ([ID], [Status], [CreatedAt])
	DEFAULT VALUES;
 /* SQLTRAN FIX: INSERT INTO ExampleTable DEFAULT VALUES; */
END;

Training video example

In the example shown, a procedure inserts a row into the Emulations.NameStatuses table. This table defines a Status column with a default value.

In the original source SQL, the INSERT INTO ... VALUES ... statement omits the Status column, relying on the database default behavior — a strategy that would fail in Fabric.

SQL Tran emulates this default behavior by rewriting the INSERT statement to explicitly provide the default value in the target SQL.

In this case, both identity column emulation and default value emulation are applied to ensure complete compatibility.

This automation eliminates the need for time-consuming manual corrections and preserves expected data behavior during migration.

Default values
Default value in table definition
Default value emulation in translated SQL