How to Insert Records from One Table into Another in SQL?

Discover how to efficiently select and insert records between SQL tables while adding fixed values with a single command.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL: select insert into select + add fixed value fron another table

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

SQL is a powerful tool for managing and manipulating data stored in tables. Every now and then, database developers face challenges that require a combination of selecting data from one table and inserting it into another. In this post, we will address a specific problem: how to select records from one table based on a condition and insert them into another table while including a fixed value.

The Scenario

Let's set the stage: You have three tables:

Table01: This table includes timestamps (timestamp_begin and timestamp_end), several columns (like column1, column2, ... columnX), and an id.

Table02: This table contains a timestamp and similar columns (column1, column2, ... columnX).

Table03: This table will ultimately be populated with the selected records from Table02 and the corresponding id from Table01.

Your goal is to select all records from Table02 where the timestamp falls between the beginning and ending timestamps in Table01, adding a fixed id value from Table01 to each record as you insert them into Table03.

The Solution

Creating a New Table

If Table03 does not exist yet, you will want to create it while inserting the records. Here's a query that accomplishes that:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Query:

INTO Table03: This phrase indicates that the results of this SELECT statement should create a new table named Table03.

Inserting into an Existing Table

If you already have Table03 created and just want to insert the records into it, use the following query:

[[See Video to Reveal this Text or Code Snippet]]

Key Changes:

INSERT INTO Table03: Here, we specify that we want to insert the selected records into an already established Table03.

Conclusion

Both scenarios of selecting and inserting records in SQL can be performed using an INSERT INTO SELECT structure. By using the INNER JOIN condition, we filter records based on timestamp ranges effectively.

This approach not only simplifies the workflow but also minimizes the need for multiple commands, allowing you to carry out your database operations in a cleaner and more efficient manner.

Feel free to adapt these queries to suit your data structures and specific requirements! Happy querying!