Tutorial: Transform data using a stored procedure

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

Learn how to create and save a new stored procedure to transform data.

Transform data

1. From the Home tab of the ribbon, select New SQL query.

   

2. In the query editor, paste the following code to create the stored procedure dbo.populate_aggregate_sale_by_city. This stored procedure will create and load the dbo.aggregate_sale_by_date_city table in a later step.

   --Drop the stored procedure if it already exists.
   DROP PROCEDURE IF EXISTS [dbo].[populate_aggregate_sale_by_city]
   GO
   
   --Create the populate_aggregate_sale_by_city stored procedure.
   CREATE PROCEDURE [dbo].[populate_aggregate_sale_by_city]
   AS
   BEGIN
       --If the aggregate table already exists, drop it. Then create the table.
       DROP TABLE IF EXISTS [dbo].[aggregate_sale_by_date_city];
       CREATE TABLE [dbo].[aggregate_sale_by_date_city]
           (
               [Date] [DATETIME2](6),
               [City] [VARCHAR](8000),
               [StateProvince] [VARCHAR](8000),
               [SalesTerritory] [VARCHAR](8000),
               [SumOfTotalExcludingTax] [DECIMAL](38,2),
               [SumOfTaxAmount] [DECIMAL](38,6),
               [SumOfTotalIncludingTax] [DECIMAL](38,6),
               [SumOfProfit] [DECIMAL](38,2)
           );
   
       --Reload the aggregated dataset to the table.
       INSERT INTO [dbo].[aggregate_sale_by_date_city]
       SELECT
           FS.[InvoiceDateKey] AS [Date], 
           DC.[City], 
           DC.[StateProvince], 
           DC.[SalesTerritory], 
           SUM(FS.[TotalExcludingTax]) AS [SumOfTotalExcludingTax], 
           SUM(FS.[TaxAmount]) AS [SumOfTaxAmount], 
           SUM(FS.[TotalIncludingTax]) AS [SumOfTotalIncludingTax], 
           SUM(FS.[Profit]) AS [SumOfProfit]
       FROM [dbo].[fact_sale] AS FS
       INNER JOIN [dbo].[dimension_city] AS DC
           ON FS.[CityKey] = DC.[CityKey]
       GROUP BY
           FS.[InvoiceDateKey],
           DC.[City], 
           DC.[StateProvince], 
           DC.[SalesTerritory]
       ORDER BY 
           FS.[InvoiceDateKey], 
           DC.[StateProvince], 
           DC.[City];
   END
   

3. To save this query for reference later, right-click on the query tab, and select Rename.

   

4. Type Create Aggregate Procedure to change the name of the query.

5. Press Enter on the keyboard or select anywhere outside the tab to save the change.

6. Select Run to execute the query.

7. Select the refresh button on the ribbon.

   

8. In the Object explorer, verify that you can see the newly created stored procedure by expanding the StoredProcedures node under the dbo schema.

   

9. From the Home tab of the ribbon, select New SQL query.

10. In the query editor, paste the following code. This T-SQL executes dbo.populate_aggregate_sale_by_city to create the dbo.aggregate_sale_by_date_city table.

    --Execute the stored procedure to create the aggregate table.
    EXEC [dbo].[populate_aggregate_sale_by_city];
    

11. To save this query for reference later, right-click on the query tab, and select Rename.

12. Type Run Create Aggregate Procedure to change the name of the query.

13. Press Enter on the keyboard or select anywhere outside the tab to save the change.

14. Select Run to execute the query.

15. Select the refresh button on the ribbon. The query takes between two and three minutes to execute.

16. In the Object explorer, load the data preview to validate the data loaded successfully by selecting on the aggregate_sale_by_city table in the Explorer.

    

Next step

Tutorial: Time travel using T-SQL at statement level