Update and Insert in BigQuery (UPSERT)

[KShah1665472180]

Hello,

I have a process that reads the data from PostgreSQL and uploads the data in BigQuery.

In order to execute this, I'm using tDBInput(PostgreSQL) --> tmap --> tBigQuerySQLrow --> tBigQueryOutput

In certain conditions, I have to Update or Insert data in BigQuery.

Does someone know what it the best way to Update/Insert data from BigQuery?

I have used this query in tBigQuerySQLRow :-

"DECLARE fields STRING;

DECLARE updates STRING;

EXECUTE IMMEDIATE (

   \"SELECT STRING_AGG(column_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Table1'\"

 ) INTO fields;

EXECUTE IMMEDIATE (

  \"\"\"WITH t AS (SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Table1')

    SELECT STRING_AGG(\"t.\"||column_name ||\" = \"|| \"s.\"||column_name) from t join t as s using(column_name)\"\"\"

 ) INTO updates;

EXECUTE IMMEDIATE \"\"\"

 MERGE `Table1` T

 USING `Table2` S

  ON T.id = S.id

 WHEN NOT MATCHED THEN

  INSERT (\"\"\"||fields||\"\"\") VALUES (\"\"\"||fields||\"\"\")\"\"\""

PFA screenshot of my Job.

Thank You.