CODEWITHSUNDEEP
sqlgoogle-bigquery
DreamingFighter
DreamingFighter

Reputation: 41

How to insert repeated data to BigQuery table using SQL

I have 2 tables with the following schemas:

  1. CLIENTS with (CLIENT_ID, CLIENT_NAME)
  2. ADDRESSES with (ADDRESS_ID, CLIENT_ID, STREET, ZIP_CODE)

Assuming 1 address belongs to 1 client and 1 client may have several addresses.

I want to create one D_CLIENTS table in BigQuery with CLIENT_ID, CLIENT_NAME, ADDRESSES (record, repeated). How do I insert data to that table using SQL's Insert into script?

Upvotes: 1

Views: 843

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59195

I'll write the query that created the table (otherwise I can not insert), but modifying it to then insert should be trivial:

CREATE TABLE `deleting.addresses`
AS

WITH clients AS (
  SELECT 1 AS id, 'Dean Finn' AS name
), addresses AS (
  SELECT * FROM UNNEST([(STRUCT(8 AS id, 1 AS client_id, "Spear" AS street, "94105" AS zip))])
)

SELECT a.id client_id, ANY_VALUE(name) name, ARRAY_AGG(STRUCT(street, zip)) addresses
FROM clients a JOIN addresses b ON a.id=b.client_id
GROUP BY 1

Upvotes: 1

Related Questions