BigQuery Hack: How to Quickly Populate a Table Without INSERT()

Reader Note: Before we begin, I have to warn you that the following is not considered to be a best SQL practice and is certainly not a textbook case of data engineering.

It’s, at best, a quick trick that will be most effective when you need to create a bit of test data from scratch when time is of the essence. Replicate at your own discretion.

Setting a Destination Table with Query Settings

BigQuery’s SQL workspace has some powerful, often-overlooked features that can help optimize your query jobs and make the tedious process of writing SQL scripts a bit more pleasant.

From providing insight into whether your query will run or not to providing support for JSON data types, BigQuery has a feature that accommodates almost every data engineering use case.

For this walkthrough, we’ll be concentrating on BigQuery’s query settings.

Just like you may not be familiar with every setting nestled in the system preferences app on your laptop (I’m a Mac person, can’t you tell?), or the settings app on your phone, I’d be willing to bet you’re not intimately familiar with BigQuery’s query settings.

Build Your Pipeline To A Data Engineering Career

You’ve reached the limit of the public preview. The full version of this post includes the implementation details: The code, the edge cases, and the "why" behind the architecture.

When you join PipelineToDE, you get:

• The DA → DE Pathway Course: A structured roadmap to bridge the gap between analysis and engineering.
• Weekly Senior Deep Dives: Fresh, tactical insights on Python, Cloud (GCP/AWS), and modern orchestration delivered every week.
• Production-Ready Blueprints: Access to 80+ protected stories and code repos from my time in the trenches as a Senior DE
• The DE Job Board (Coming Soon): Exclusive access to a curated board of high-agency Data Engineering roles.

To access the settings, when you’re in the SQL workspace, click the gear shaped ‘More’ tab.

The ‘More’ menu drops down to reveal query settings.

Here is where we can set the job type, choosing between batch (more performative) or interactive (more immediate), and control how the query saves our results. For our purposes, we’d like to change the destination for our query’s results from ‘Save query results in a temporary table’ to ‘Set a destination table for query results.’

We’ll set our results to write to a dataset I created, ‘sample_dataset’, and the table, ‘sample_table.’

Generate Test Values

Now, I’ll write a quick query with some fake values. Since this whole exercise is an in-a-pinch response to needing to generate test values, I decided I’d abide by SQL best practice and at least preemptively filter for duplicate values.

To illustrate this concept, I created a quick CTE with some mock values. For context, here is the desired output.

After conceptualizing and formatting the query, it’s time to adjust the query settings to ensure our data is written properly. In query settings under Destination: Select ‘Set a destination table for query results’ and ‘Destination table write preference — Write if empty.’

Now, when you return to the SQL workspace, you’ll notice new designations under your query, alerting you that the query results will be saved in the aforementioned destination table with the preference of write if empty.

Running the query has the same effect as the INSERT() statement would have. It inserts the written values into the schema I defined for ‘sample_table.’

Verify Results

Unlike the CTE that I used to create this simple table, these results are permanent. To verify, you can run a simple query.

The values exist!

Please Try This at Home — and at Work

Knowing how to change the settings and write properties of your BigQuery queries can result in efficient, powerful SQL at work or in personal projects.

Writing to a destination table eliminates the need to ensure cumbersome CREATE TABLE, DELETE FROM or INSERT statements correspond with a given schema. Additionally, query settings can help BigQuery users accomplish tasks that the platform doesn’t support natively, like the renaming of existing columns.

Keep Ingesting

You just finished a deep dive into this topic, which is one piece of the larger engineering puzzle. If you're ready to stop browing and start following a structured roadmap, head over to the DA → DE Pathway Course.

Your Recommended Module

• Module 2: Advanced SQL for DE— Master complex transformations, window functions, and query optimization.
• Full Course Index — Browse all modules and architectural patterns included in your membership.

Remember: As a member, you have full access to the source code for every project in the course. No extra fees, just execution.