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:
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!
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”?
Hi, I am not quite sure what your question is. Could you provide some more details on what you are looking for?
-Steve Stedman