CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
dks551
dks551

Reputation: 1113

Conditional insert into table in Snowflake

Let's say I have 2 tables TABLE_A and TABLE_B. When there is no row in TABLE_B I want to insert a dummy row in TABLE_A and if there is any row in TABLE_B I want to insert whatever is present into TABLE_A. How can we do this ?

CREATE TABLE TABLE_A(
    id          STRING      NOT NULL
  , uid         STRING      NOT NULL
  , date        STRING      NOT NULL
  , timestamp   TIMESTAMP   NOT NULL
  , payload     OBJECT      NOT NULL
  , arr         ARRAY       NOT NULL
)
;

CREATE TABLE TABLE_B(
    id          STRING      NOT NULL
  , uid         STRING      NOT NULL
  , date        STRING      NOT NULL
  , timestamp   TIMESTAMP   NOT NULL
  , payload     OBJECT      NOT NULL
  , arr         ARRAY       NOT NULL
)
;

When COUNT(*) = 0 in TABLE_B Then Insert 'dummy_id','dummy_uid', current_date(), current_timestamp(), TO_OBJECT(PARSE_JSON('{}')), TO_ARRAY('DUMMY_ROWS') into TABLE_A.

Upvotes: 3

Views: 7137

Answers (2)

Marcin Zukowski
Marcin Zukowski

Reputation: 4719

How about

INSERT INTO TABLE_A
  SELECT * FROM TABLE_B
UNION ALL
  SELECT 
    'dummy_id','dummy_uid', current_date(), current_timestamp(),
    TO_OBJECT(PARSE_JSON('{}')), TO_ARRAY('DUMMY_ROWS') 
  WHERE (SELECT COUNT(*) FROM TABLE_B) = 0

Only one part of UNION ALL will contain something.

Btw, this solution should in general work for any DBMS, not only Snowflake.

Upvotes: 4

Ashwani Tiwari
Ashwani Tiwari

Reputation: 1567

You can use the CASE function of SQL , you can write something like that

INSERT into table_A(),
CASE
WHEN (select count(*) from table_b ) > 0 
THEN select * from table_b
ELSE "some dummy data"
END
FROM table_b;

This is not the copy paste answer but from this you can find your path

Upvotes: -1

Related Questions