Replace or update table while using a with clause

Question

I have a currency converter table where I'm missing dates on weekends. Using this query solves this by adding Fridays value to the following Saturday and Sunday. I've created this query that returns the table I would like to be my new currency_converter table. How can I save it as currency_converter_new? my table looks like this right now:

currency_converter:

time_period	obs_value	currency
20.02.2023	10,9683	EUR
20.02.2023	147,3	DKK
17.02.2023	11,015	EUR
17.02.2023	147,92	DKK

This is the output I would like to have in my other table. currency_converter_new:

time_period	obs_value	currency
20.02.2023	10,9683	EUR
20.02.2023	147,3	DKK
19.02.2023	11,015	EUR
19.02.2023	147,92	DKK
18.02.2023	11,015	EUR
18.02.2023	147,92	DKK
17.02.2023	11,015	EUR
17.02.2023	147,92	DKK

Using the statement below, I'm able to achieve what I want, but not sure how to get this to replace my currency_converter_new table.

with currency AS
(SELECT *, LEAD(time_period) OVER (PARTITION BY valuta ORDER BY time_period) as next_time_period
  FROM currency_converter
  )
 SELECT c.day as time_period, t.obs_value, t.valuta
 FROM dim_calendar c
 JOIN currency t
 ON c.day BETWEEN t.time_period and ISNULL(DATEADD(day, -1, t.next_time_period), t.time_period)

Any suggestions on how to solve this?

I have tried using the INSERT INTO, but can't seem to get that to work. I also couldn't get this syntax to work. tried adding INSERT INTO before my SELECT like this:

with currency AS
(SELECT *, LEAD(time_period) OVER (PARTITION BY valuta ORDER BY 
time_period) as next_time_period
FROM currency_converter
);
INSERT INTO (currency_converter(time_period, obs_value, valuta)
SELECT * FROM ( 
SELECT c.day as time_period, t.obs_value, t.valuta
FROM dim_calendar c
JOIN currency t
ON c.day BETWEEN t.time_period and ISNULL(DATEADD(day, -1, 
t.next_time_period), t.time_period)
)

I will truncate my currency_converter_new table before running this.

just wrap your select in a subquery and select from it, something like: with currency AS (...) insert into currency_converter (time_period, obs_value, valuta) select * from ( SELECT c.day as time_period, t.obs_value, t.valuta FROM dim_calendar c...) x — siggemannen, Feb 23 '23 at 11:04
You have an incorrect query syntax, see my answer below. I supplemented the answer with the insertion of specific columns (see 2th example 'insert into'), try to rewrite the query yourself according to the example. — Sergey Zakharov, Feb 23 '23 at 14:51

Sergey Zakharov · Answer 1 · 2023-02-23T14:50:40.973

To be honest, it's not clear what you wanted to show, because this is some kind of torn-out request without context.

But if I understand correctly- do you want to insert or update from the query using the WITH expression?

See the example below:

DROP TABLE IF EXISTS #tt;

CREATE TABLE #tt (dt DATETIME);

-- inserting into a table from a 'SELECT' with the expression 'WITH'
WITH t AS (SELECT getdate() dt)
INSERT INTO #tt
SELECT * FROM t;

-- to insert specific columns, you can write like this
WITH t AS (SELECT getdate() dt)
INSERT INTO #tt (dt)
SELECT dt FROM t;

SELECT * FROM #tt;

-- update the table with FROM, JOIN and with the expression 'WITH'
WITH t AS (SELECT getdate() dt)
UPDATE #tt
  SET dt = DATEADD(millisecond,100,t.dt)
  FROM #tt, t

SELECT * FROM #tt;

WITH t AS (SELECT getdate() dt)
UPDATE #tt
  SET dt = DATEADD(millisecond,200,t.dt)
  FROM #tt
  JOIN t ON 1=1

SELECT * FROM #tt;

DROP TABLE IF EXISTS #tt;

PS: the temporary table is here for an example, you can use your table and your query, I showed you how to use the WITH expression in table insertion and update operations.

PPS: also using WITH for the MERGE statement

WITH table_3 AS (
  SELECT ...
)
MERGE table t
  USING (
    SELECT
        *
      FROM table_2
      JOIN table_3 ON <join condition>
      WHERE <where condition>
  ) tt
  ON <join condition for "merging" table and query>
  WHEN MATCHED [AND <addition condition>]
    THEN DELETE
  WHEN MATCHED [AND <addition condition>]
    THEN UPDATE
      SET
        t.columns = tt.columns
        ...
  WHEN NOT MATCHED
    THEN INSERT
        (...)
      VALUES
        (tt.columns, ...)
  OUTPUT
     $action,
     inserted.*,
     deleted.*
;

for some more detailed solution, you need to describe your question in detail and an example of the data

daffy_daf · Answer 2 · 2023-02-24T09:17:50.903

It was as easy as just adding a INSERT INTO statement before the with clause.

    INSERT INTO currency_converter_new(time_period, obs_value, valuta)
with currency AS
(
SELECT *, LEAD(time_period) OVER (PARTITION BY valuta ORDER BY time_period) as next_time_period
  FROM currency_converter
  )
 SELECT c.day as time_period, t.obs_value, t.valuta
 FROM dim_calendar c
 JOIN currency t
 ON c.day BETWEEN t.time_period and ISNULL(DATEADD(day, -1, t.next_time_period), t.time_period)

Replace or update table while using a with clause

2 Answers2