CODEWITHSUNDEEP
sqlinsertcasesnowflake-cloud-data-platform
JMV12
JMV12

Reputation: 1035

Using Case Statement To Decide Column To Insert Into

I have data that is being inserted from one table to another. During that insert, there are certain transformations happing to some of the data. One example is

SELECT COLUMN_NAME
CASE
    WHEN year(DATE_COLUMN) >= 1000 THEN (Insert into columnA of TARGET_TABLE)
    ELSE (Insert into columnB of TARGET_TABLE)
END
FROM SOURCE_TABLE

There are many other columns being inserted into the target table from the source table, but whether the date goes into columnA or columnB depends on the date in the source table's DATE_COLUMN. How would I make sure that the date is being filled in the correct column of the target table following this method?

Upvotes: 1

Views: 1613

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You need two CASE expressions, one for each column:

INSERT INTO target_table (col_name, column_a, column_b)
SELECT 
  column_name,
  CASE WHEN year(date_column) >= 1000 THEN some_column END,
  CASE WHEN year(date_column) < 1000 OR date_column IS NULL THEN some_column END
FROM source_table;

Upvotes: 2

Related Questions