1

is it possible to load data into multiple tables using INSERT ALL without adding duplications or without using overwrite to accomplish it?

As WHEN clause doesn't support subqueries unless it returns a value to compare with something else, i am currently trying with the following:

INSERT ALL
INTO TEMP (PKEY, TITLE) VALUES ( ID, TITLE_NAME )
SELECT
$1 AS ID,
$2 AS TITLE_NAME
FROM @azure_stg/mti_test.csv (FILE_FORMAT=>'CSV')
WHERE $1 NOT IN (SELECT PKEY FROM TEMP)

my aim is to add another 3 tables to be filled with data in parallel within the same query without adding duplications. so I tried to add another into table2 select ... but it didn't work.

alim1990
  • 4,656
  • 12
  • 67
  • 130
  • So the question is: Is it possible to insert into multiple tables in parallel without using the conditional multi-table insert? – Marcel Nov 15 '21 at 12:38
  • @Marcel and specify only unavailable rows for each table. – alim1990 Nov 15 '21 at 13:41
  • Typically in such situation you have in you input: `table_name` and `ID`. You should discard all `ID`s that exists in the corresponding `table`. What do you *expect* to do if you have only list od `ID`s? – Marmite Bomber Nov 15 '21 at 18:51

1 Answers1

1

If I got your question correct, you want to insert into several tables with one statement and without any condition. Main consideration is: Are all INSERT statements using the same source data or different source data?

If all statements are using the same source data, this is the syntax:

insert all
  into t1
  into t1 (c1, c2, c3) values (n2, n1, default)
  into t2 (c1, c2, c3)
  into t2 values (n3, n2, n1)
select n1, n2, n3 from src;

See here: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table.html

If the statements are based on different SELECT-statements, you have to split them up into single INSERT statements.

Marcel
  • 2,454
  • 1
  • 5
  • 13
  • this works for PIT tables in data vault. i need to load in parallel into tables without duplications. – alim1990 Nov 15 '21 at 13:42