Replace or update table while using a with clause

vatpfxk5  于 2023-02-28  发布在  其他
关注(0)|答案(2)|浏览(148)

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_periodobs_valuecurrency
20.02.202310,9683EUR
20.02.2023147,3DKK
19.02.202311,015EUR
19.02.2023147,92DKK
18.02.202311,015EUR
18.02.2023147,92DKK
17.02.202311,015EUR
17.02.2023147,92DKK

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.

h79rfbju

h79rfbju1#

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

mlnl4t2r

mlnl4t2r2#

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)

相关问题