How to INSERT INTO in ONE TABLE from MULTIPLE TABLES?

Question

I will make you an example with GRADES MINUTES and SECONDS on three different tables.

T_GRADES
ID_GRADE
- Primary Key
- Autoincrement
GRADE
- Numeric (all numbers from 0 to 359)

T_MINUTES
ID_MINUTE
- Primary Key
- Autoincrement
MINUTE
- Numeric (all numbers from 0 to 59)

T_SECONDS
ID_SECOND
- Primary Key
- Autoincrement
SECOND
- Numeric (all numbers from 0 to 59)

With this query I can see all data from all three tables.

SELECT "T_GRADES"."GRADE", "T_MINUTES"."MINUTE", "T_SECONDS"."SECOND"
FROM "T_GRADES" "T_GRADES", "T_MINUTES" "T_MINUTES", "T_SECONDS" "T_SECONDS"
ORDER BY "GRADO", "PRIMO", "SECONDO"

Now, how can I select INSERT INTO a 4th TABLE called T_GMS with these fields like a CROSS JOIN between all three tables?

T_GMS
ID_GMS
- Primary Key
- Autoincrement
GRADE
- Numeric (all numbers from 0 to 359)
PRIME
- Numeric (all numbers from 0 to 59)
SECOND
- Numeric (all numbers from 0 to 59)

Thank you so much in advance for your answers.

Unrelated to the question, but: https://dba.stackexchange.com/questions/154251/ — , Jan 04 '20 at 08:15

score 0 · Answer 1 · answered Jan 04 '20 at 07:21

0

To create a new table from the results of a query you can use INTO [Table] command before FROM. e.g

SELECT * INTO [Tbl_NewTable] FROM [tbl_User],[tbl_Role]

If your table already exists, you have to use INSERT INTO [Table] command before SELECT

INSERT INTO [Tbl_NewTable] SELECT * FROM [tbl_User],[tbl_Role]

make sure your selected columns in query results must be same as the NewTable columns

answered Jan 04 '20 at 07:21

Owais Parkar

1

Owais, I must select "three fields" each from "three" different tables and not from just "one" table, – Luca DEL PARTITA Jan 04 '20 at 07:57
To create a new table from a SELECT statement you need to use `create table .. as select ..`in "SQL" - you wrote a T-SQL answer, but the `sql` tag refers to standard SQL, not a specific DBMS product – Jan 04 '20 at 08:13
@LucaDELPARTITA yes, you can select columns from different table – Owais Parkar Jan 04 '20 at 08:26

score 0 · Answer 2 · answered Jan 04 '20 at 08:11

Assuming your 4th table exists, you can simply do

INSERT INTO T_GMS ("GRADE", "PRIME", "SECOND")
SELECT "GRADE", "MINUTE", "SECOND"
FROM "T_GRADES", "T_MINUTES", "T_SECONDS"
ORDER BY "GRADE", "MINUTE", "SECOND"

The primary key will be generated automatically, and you specify which values go into which columns by the order of the columns listed in the brackets after T_GMS.

I cut out all the aliases for simplicity (I do not think you need them). Also your SQL seemed a little confused on naming, so please check.

How to INSERT INTO in ONE TABLE from MULTIPLE TABLES?

2 Answers2