CTE – With An Insert Statement

Download PDF

Queries with Common table expressions (CTE) are made up of two parts, the CTE part, and the SQL that references the CTE.  In preparation for SQL Saturday, the question came up of can you use an INSERT or UPDATE statement with a CTE.  Referring to the documentation I confirmed that using an insert or update inside of the CTE is invalid, but you can use an insert or update statement outside of the CTE.

 

For Example.


DECLARE @NumTableVar TABLE( n INT);

 

;WITH numbers (n)
AS (SELECT 1
UNION ALL
SELECT 1 + n
FROM   numbers
WHERE  n < 1000)

INSERT INTO @NumTableVar   (n)
SELECT n
FROM   numbers
OPTION (MAXRECURSION 0);


SELECT *
FROM   @NumTableVar;

When run confirms that you can use the insert statement with a CTE, but not inside of a CTE.

 

This would be very useful if you had just created a table and wanted to fill it up quickly for testing purposes.

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

2 Comments on “CTE – With An Insert Statement

  1. ALTER PROCEDURE [dbo].[POPULATE_FAIT_STOCK_HERMES]
    AS
    BEGIN

    /**** ETL_HISTORY****/
    DECLARE @STARTIME DATETIME
    DECLARE @ENDTIME DATETIME
    DECLARE @USER NVARCHAR(30)
    DECLARE @PROCEDURENAME NVARCHAR(30)
    DECLARE @DURATION INT

    SET @STARTIME = getdate()
    SET @USER = (SELECT SYSTEM_USER)
    SET @PROCEDURENAME = ‘FAIT_STOCK_HERMES’

    — ————————————————-
    — POPULATE POPULATE FAIT_STOCK_HERMES
    — ————————————————-
    INSERT INTO DW_DEV.dbo.FAIT_STOCK_HERMES

    /* SELECT
    CAST((CAST((FLOOR(sp.[id_stock])) as INT)) as nvarchar(10)) as ID_STOCK,
    isNull(sp.[id_partenaire],0) as ID_PARTENAIRE,
    CAST((CAST((FLOOR(sp.[id_equipage])) as INT)) as nvarchar(10)) as ID_EQUIPAGE,
    — T.ID_DIM_TEMPS,
    — FORMAT(sp.[date_modif]), ‘yyyyMMdd’) AS ID_DIM_TEMPS,
    — FORMAT(convert(date,date_bon_de_commande), ‘yyyyMMdd’)
    — FORMAT(date_visite, ‘yyyyMMdd’) ID_DIM_TEMPS,
    FORMAT(sp.[date_modif], ‘yyyyMMdd’) ID_DIM_TEMPS,
    P.ID_PRODUIT,
    ((sp.[quantité])) as QTE_STOCK,
    q.nom_equipage as [NOM EQUIPAGE],
    getdate() as LAST_UPDATE_DATE
    FROM [STAGING].[stgJB].[t_stock_t_produit] sp

    inner join [STAGING].[stgJB].[t_equipage] q on q.ID_EQUIPAGE = sp.id_equipage
    inner join DW_DEV.dbo.DIM_PRODUIT_HERMES P on P.ID = sp.id_produit
    — inner join DW_DEV.dbo.DIM_TEMPS T on T.[Date] = convert(date, sp.[date_modif], 121)
    — ((convert(datetime, [date_modif], 121))) as [date_modif] */

    With DT as (
    SELECT
    CAST((CAST((FLOOR(sp.[id_stock])) as INT)) as nvarchar(10)) as ID_STOCK,
    isNull(sp.[id_partenaire],0) as ID_PARTENAIRE,
    CAST((CAST((FLOOR(sp.[id_equipage])) as INT)) as nvarchar(10)) as ID_EQUIPAGE,
    FORMAT(sp.[date_modif], ‘yyyyMMdd’) ID_DIM_TEMPS,
    — P.ID_PRODUIT,
    ((sp.[quantité])) as QTE_STOCK,
    — q.nom_equipage as [NOM EQUIPAGE],
    getdate() as LAST_UPDATE_DATE
    FROM [STAGING].[stgJB].[t_stock_t_produit] sp

    inner join [STAGING].[stgJB].[t_equipage] q on q.ID_EQUIPAGE = sp.id_equipage
    inner join DW_DEV.dbo.DIM_PRODUIT_HERMES P on P.ID = sp.id_produit )

    MAX_DT as (
    SELECT CONVERT(VARCHAR,MAX([date_modif]),121) as max_dt
    ,[id_equipage]
    ,Concat(CONVERT(VARCHAR,MAX([date_modif]),121),[id_equipage]) as key_
    FROM DT
    GROUP BY [id_equipage]

    SELECT DISTINCT MAX_DT.[id_equipage]
    ,DT.[id_stock]
    ,Case when DT.id_partenaire=’0′ then NULL else DT.id_partenaire end as id_partenaire
    ,MAX_DT.max_dt as [date_modif],
    DT.quantité

    FROM MAX_DT
    join DT on MAX_DT.key_= DT.key_

    ORDER BY MAX_DT.[id_equipage] DESC

    SET @ENDTIME = getdate()
    SET @DURATION = DATEDIFF( minute, @STARTIME, @ENDTIME)

    INSERT INTO DW_DEV.dbo.ETL_HISTORY
    ( START_TIME,
    END_TIME,
    [PROCEDURE_NAME],
    [USER],
    DURATION)
    VALUES (@STARTIME, @ENDTIME,@PROCEDURENAME, @USER, @DURATION )
    END

    how to do the code with “with”?

Leave a Reply

Your email address will not be published. Required fields are marked *

*