'SQLite - INSERT INTO SELECT - how to insert data of "join of 3 existing tables into a new table"?

So the scenario here is, I have 4 tables in the database namely:

  1. "question_info":
    CREATE TABLE question_info ( q_id mediumint(9) NOT NULL, q_type_id int(11) NOT NULL, q_options_id mediumint(9) NOT NULL, q_category_id int(11) NOT NULL, q_text varchar(2048) NOT NULL, status tinyint(4) NOT NULL DEFAULT '0', q_date_added date NOT NULL DEFAULT '2013-01-01', q_difficulty_level tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY(q_id) );

  2. "question_options_info":
    CREATE TABLE question_options_info ( q_options_id mediumint(9) NOT NULL, q_options_1 varchar(255) NOT NULL, q_options_2 varchar(255) NOT NULL, q_options_3 varchar(255) NOT NULL, q_options_4 varchar(255) NOT NULL, q_options_ex_1 varchar(1024) DEFAULT NULL, q_options_ex_2 varchar(1024) DEFAULT NULL, q_options_ex_3 varchar(1024) DEFAULT NULL, q_options_ex_4 varchar(1024) DEFAULT NULL, PRIMARY KEY(q_options_id) );

  3. "question_answer_info":
    CREATE TABLE question_answer_info ( q_id mediumint(9) NOT NULL, q_options mediumint(9) NOT NULL );

  4. "trivia_data":
    CREATE TABLE trivia_data ( q_id mediumint(9) NOT NULL, q_text varchar(2048) NOT NULL, q_options_1 varchar(255) NOT NULL, q_options_2 varchar(255) NOT NULL, q_options_3 varchar(255) NOT NULL, q_options_4 varchar(255) NOT NULL, q_options mediumint(9) NOT NULL, q_difficulty_level tinyint(4) NOT NULL DEFAULT '0', q_date_added date NOT NULL DEFAULT '2015-04-8', PRIMARY KEY(q_id) );

So what I need is to, insert a data into trivia_data table. The data is returned by this query:

SELECT question_info.q_id, question_info.q_text, question_options_info.q_options_1, question_options_info.q_options_2, question_options_info.q_options_3, question_options_info.q_options_4, question_answer_info.q_options, question_info.q_difficulty_level, question_info.q_date_added
FROM question_info JOIN question_options_info ON question_info.q_options_id = question_options_info.q_options_id JOIN question_answer_info ON question_info.q_id = question_answer_info.q_id;

This query would return data somewhat like this: enter image description here

I have already tried this specific query to insert the data:
INSERT INTO trivia_data VALUES(q_id, q_text, q_options_1, q_options_2, q_options_3, q_options_4, q_options, q_difficulty_level, q_date_added) SELECT question_info.q_id, question_info.q_text, question_options_info.q_options_1, question_options_info.q_options_2, question_options_info.q_options_3, question_options_info.q_options_4, question_answer_info.q_options, question_info.q_difficulty_level, question_info.q_date_added FROM question_info JOIN question_options_info on question_info.q_options_id = question_options_info.q_options_id JOIN question_answer_info on question_info.q_id = question_answer_info.q_id;

But it always returns this error:
near "SELECT": syntax error:

Honestly I am a novice to SQL. So please try to explain as simply as possible. Any help would be appreciated. Thank You.

sqldatabasesqlitesql-insert


Solution 1:[1]

You don't need the VALUES keyword, as you are selecting from a query:

INSERT INTO trivia_data (
    q_id, 
    q_text, 
    q_options_1, 
    q_options_2, 
    q_options_3, 
    q_options_4, 
    q_options, 
    q_difficulty_level, 
    q_date_added)  
SELECT 
    question_info.q_id, 
    question_info.q_text, 
    question_options_info.q_options_1, 
    question_options_info.q_options_2, 
    question_options_info.q_options_3, 
    question_options_info.q_options_4, 
    question_answer_info.q_options, 
    question_info.q_difficulty_level, 
    question_info.q_date_added 
FROM question_info 
    JOIN question_options_info on question_info.q_options_id = question_options_info.q_options_id 
    JOIN question_answer_info on question_info.q_id = question_answer_info.q_id;

In general if you are inserting a record then the syntax is 

INSERT INTO <tablename> (<column1>, <column2>, ..., <columnN>)
VALUES (<value1>, <value2>, ..., <valueN>)

If you are inserting the results the syntax is like this:

INSERT INTO <tablename> (<column1>, <column2>, ..., <columnN>)
SELECT <value1>, <value2>, ..., <valueN> FROM ...

As you see there is no VALUES keyword in this case

Solution 2:[2]

Remove VALUES from your SQL as the values come from SELECT in this case.

INSERT INTO trivia_data (
  q_id,
  q_text,
  q_options_1,
  q_options_2,
  q_options_3,
  q_options_4,
  q_options,
  q_difficulty_level,
  q_date_added
)
SELECT
  question_info.q_id,
  question_info.q_text,
  question_options_info.q_options_1,
  question_options_info.q_options_2,
  question_options_info.q_options_3,
  question_options_info.q_options_4,
  question_answer_info.q_options,
  question_info.q_difficulty_level,
  question_info.q_date_added
FROM question_info
JOIN question_options_info
  ON question_info.q_options_id = question_options_info.q_options_id
JOIN question_answer_info
  ON question_info.q_id = question_answer_info.q_id;

Solution 3:[3]

Remove the VALUES keyword. Try this:

INSERT INTO trivia_data (q_id, q_text, q_options_1, q_options_2, 
    q_options_3, q_options_4, q_options, q_difficulty_level, q_date_added)  
SELECT question_info.q_id, question_info.q_text, 
    question_options_info.q_options_1, question_options_info.q_options_2, 
    question_options_info.q_options_3, question_options_info.q_options_4, 
    question_answer_info.q_options, question_info.q_difficulty_level, 
    question_info.q_date_added FROM question_info 
    JOIN question_options_info on question_info.q_options_id = question_options_info.q_options_id 
    JOIN question_answer_info on question_info.q_id = question_answer_info.q_id;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 dotnetom
Solution 2 Mike Brant
Solution 3 Rahul Tripathi

Related Questions