How to insert CDC Data from a stream to another table with dynamic column names

user_12916077 0
Viewed 56

I have a Snowflake stored procedure and I want to use "insert into" without hard coding column names.

INSERT INTO MEMBERS_TARGET (ID, NAME) 
    SELECT ID, NAME 
    FROM MEMBERS_STREAM;

This is what I have and column names are hardcoded. The query should copy data from MEMBERS_STREAM to MEMBERS_TARGET. The stream has more columns such as

METADATA$ACTION | METADATA$ISUPDATE | METADATA$ROW_ID

which I am not intending to copy.

cdc snowflake-cloud-data-platform insert stream
1 Answers

I don't know of a way to not copy the METADATA columns if not hardcoding. However if you don't want the data maybe the easiest thing to do is to add them to your target, INSERT using a SELECT * and later in the sp set them to NULL.

Alternatively, earlier in your sp, run an ALTER TABLE ADD COLUMN to add the columns, INSERT using SELECT * and then after that run an ALTER TABLE DROP COLUMN to remove the columns? That way your table structure stays the same, albeit briefly it will have some extra columns.

A SELECT * is not usually recommended but it's the easiest alternative I can think of

user_141005830
Related