Ingest data into your Warehouse using Transact-SQL

Applies to: ✅ Warehouse in Microsoft Fabric

The Transact-SQL language offers options you can use to load data at scale from existing tables in your lakehouse and warehouse into new tables in your warehouse. These options are convenient if you need to create new versions of a table with aggregated data, versions of tables with a subset of the rows, or to create a table as a result of a complex query. Let's explore some examples.

Choose a T-SQL ingestion statement

The T-SQL language provides a rich set of statements that you can use to ingest and transform data in your warehouse. You can load data into new or existing tables, append new rows, transform data during ingestion, or synchronize changes with existing data. Warehouse in Microsoft Fabric supports the following T-SQL ingestion statements:

  • CREATE TABLE AS SELECT (CTAS) statement that allows you to create a new table in your warehouse from the output of a SELECT statement.
  • SELECT INTO query clause that enables you to select results from any table source, and redirect the results into a new table. This is a standard feature in T-SQL language.
  • INSERT ... SELECT appends query results to the destination table. Use this statement when you want to load a new batch or an incremental subset into a table whose schema you control.
  • MERGE compares source and destination rows and conditionally inserts, updates, or deletes destination rows. Use this statement when you need to synchronize new and changed rows, or reconcile a destination with a complete source snapshot.

Choose a statement based on whether the destination table already exists and whether you want to create, append, or synchronize data. The source of each statement is a T-SQL query. You can use supported expressions, functions, filters, joins, and aggregations to shape the data during ingestion. You can use the following data sources to read the data in your T-SQL statements:

  • Reading a warehouse table, such as a staging table.
  • Reading a Lakehouse Delta Lake folder using an autogenerated table in SQL analytics endpoint for Lakehouse.
  • Reading CSV, Parquet, or JSONL files directly from Azure Data Lake or Azure Blob storage using the OPENROWSET function.

Use CTAS to create a table

CTAS creates the destination table and loads the query results in one operation. The first example shows how to create a new table that's a copy of the existing dbo.TaxiTrips table, but filtered to include only data from the year 2023:

CREATE TABLE dbo.TaxiTrips_2023
AS
SELECT * 
FROM dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Use CTAS to create and populate a new table from query results. This method is useful when the destination table doesn't exist or when you want to materialize transformed or combined data from one or more sources.

Use SELECT INTO to create a table

SELECT ... INTO also creates a destination table from query results:

SELECT * 
INTO dbo.TaxiTrips_2023
FROM MyLakehouse.dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Both CTAS and SELECT ... INTO infer destination columns from the query result. Explicitly cast expressions when the destination requires a specific data type, precision, or length.

Use INSERT ... SELECT to append data

INSERT ... SELECT loads query results into an existing table. You can append data to a table by transforming data from an external, publicly available Parquet file:

INSERT INTO dbo.<table name>
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.parquet') AS data;

Use MERGE to synchronize data

MERGE matches source and destination rows by a stable key. The following pattern assumes that dbo.DimCustomer already exists with the columns used by the statement. Ensure that the source returns no more than one row for each destination key.

MERGE dbo.DimCustomer AS target
USING staging.Customers AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED AND source.ModifiedDate > target.ModifiedDate THEN
    UPDATE SET CustomerName = source.CustomerName, City = source.City,
        ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, CustomerName, City, ModifiedDate)
    VALUES (source.CustomerID, source.CustomerName, source.City, source.ModifiedDate);

Use WHEN NOT MATCHED BY SOURCE THEN DELETE only when the source query represents the complete authoritative dataset for the rows in scope. Don't use that clause with an incremental source batch, because destination rows absent from the batch could be deleted.

Choose a data source

The ingestion statements work consistently across supported source types. Only the source reference changes.

Files in a lake

Use OPENROWSET to read Parquet, CSV, or JSONL files in OneLake, Azure Data Lake Storage, or Azure Blob Storage. You can target one file, multiple files with wildcards, or partitioned folders.

For file paths, schema inference, partition filtering, metadata functions, and examples for all four ingestion statements, see Ingest and transform lake data with OPENROWSET and T-SQL.

Delta tables in a lakehouse

The SQL analytics endpoint of a lakehouse exposes Delta tables in its /Tables area. From a warehouse in the same workspace, reference a table by using the three-part name <lakehouse>.<schema>.<table>. This isn't limited to Delta tables physically stored in the lakehouse. Tables exposed through OneLake shortcuts in the /Tables area can also reference data stored elsewhere, including the lakehouse /Files area, external storage such as ADLS or Amazon S3, mirrored data, and OneLake data in other workspaces. By using this architecture, you can query shortcut-backed data through the lakehouse SQL analytics endpoint without copying the data into the lakehouse.

For table creation, incremental loads, and synchronization examples, see Ingest Delta Lake tables with T-SQL.

Tables in the current warehouse

Use a two-part name such as <schema>.<table> to ingest data from a table in the current warehouse.

For Bronze-to-Silver-to-Gold examples that join, aggregate, and incrementally load tables inside one warehouse, see Ingest from warehouse tables and mirrored databases with T-SQL.

Mirrored databases and other workspace items

To ingest data from a mirrored database, another warehouse, or another supported item in the same workspace, add the item to Explorer and use the three-part name <item>.<schema>.<table>. To learn more about cross-warehouse queries, see Write a cross-database SQL Query. For examples using both naming patterns, see Ingest from warehouse tables and mirrored databases with T-SQL.

Audit and monitor T-SQL ingestion

CTAS, SELECT ... INTO, INSERT ... SELECT, and MERGE operations appear in the warehouse query history/activity. Use query insights to review execution details and troubleshoot ingestion queries.

Data ingestion options

Use T-SQL ingestion when the load includes filtering, type conversion, joins, calculations, aggregation, or synchronization. Choose another option when: Other ways to ingest data into your warehouse include:

Related content

  • Last updated on