3.3.3. T-SQL Transformations (COPY, CTAS)

💡 First Principle: T-SQL provides set-based transformations optimized for relational data. CTAS (CREATE TABLE AS SELECT) combines table creation and population in a single, efficient operation.

CTAS (CREATE TABLE AS SELECT)

• Purpose: Create new table from query results
• Benefit: Combines creation and population in single operation
• Use Case: Materializing transformed data, creating summary tables

-- Create aggregated sales table using CTAS
CREATE TABLE dbo.MonthlySalesSummary AS
SELECT 
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    Region,
    SUM(SalesAmount) AS TotalSales,
    COUNT(*) AS OrderCount
FROM dbo.FactSales
GROUP BY YEAR(OrderDate), MONTH(OrderDate), Region;

Cross-item (cross-database) queries: a warehouse query, CTAS or view can join lakehouse tables through their SQL analytics endpoint with three-part names (LakehouseName.dbo.Sales), in place and without a copy, within the same workspace. To reach another workspace, add a lakehouse shortcut first. T-SQL can only create tables in a warehouse; the lakehouse endpoint is read-only.

Choosing the Loading Statement

MERGE INTO dbo.Customers AS target
USING staging.Customers AS source
    ON target.CustomerID = source.CustomerID
WHEN MATCHED THEN UPDATE SET target.Email = source.Email
WHEN NOT MATCHED THEN INSERT (CustomerID, Email) VALUES (source.CustomerID, source.Email);

Set-Based Query Patterns