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;