How to replicate dummy data inside a table?

April 4, 2023

I have a BQ table with several hundreds of rows of dummy data but it’s not enough for my needs. I’m looking for a way to quickly replicate the data multiple times, as in for example copy each row 10 times and add the new rows to the existing table. What’s the SQL to do it at once?

>Solution :

for example copy each row 10 times and add the new rows to the existing table.

You can use an array for intentional duplication like below.

SELECT t.* FROM `your_table` t, UNNEST(GENERATE_ARRAY(1, 10))

For example, below sample query will duplicate sample_table 3 times.

WITH sample_table AS (
  SELECT 1 id, 'foo' name UNION ALL
  SELECT 2 id, 'bar' name 
)
SELECT t.* FROM `sample_table` t, UNNEST(GENERATE_ARRAY(1, 3))

--Query results
+----+------+
| id | name |
+----+------+
|  1 | foo  |
|  1 | foo  |
|  1 | foo  |
|  2 | bar  |
|  2 | bar  |
|  2 | bar  |
+----+------+