🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

Basic Syntax rule for insert command "Insert into … values ( SELECT … FROM … ) " ?

I am trying to INSERT INTO a table using the input from another table in same database.
I always get confused to remember the correct syntax for the SQL engine of the day (MySQL, Oracle, SQL Server, Informix, and DB2).
Is there a silver-bullet syntax coming from an SQL standard that would allow me to insert the values without worrying about the database?
syntax database sql
by

3 Answers

kshitijrana14
Try:
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
This is standard ANSI SQL and should work on any DBMS
It definitely works for:
Oracle
MS SQL Server
MySQL
Postgres
SQLite v3
Teradata
DB2
Sybase
Vertica
HSQLDB
H2
AWS RedShift
SAP HANA
Google Spanner
Shahlar1vxp
The basic syntax rule goes without specifying the columns in the INSERT INTO* part in case you are supplying values for all the columns in the *SELECT part. The code is as follows-
INSERT INTO table1

SELECT  col1, col2

FROM    table2

But this will not work since the value for col2 is not specified.
Now, by using the MS SQL server, 
INSERT INTO table1

SELECT  col1

FROM    table2
RoliMishra
To get only one value in a multi value INSERT from another table I did the following in SQLite3:

INSERT INTO column_1 ( val_1, val_from_other_table ) 

VALUES('val_1', (SELECT  val_2 FROM table_2 WHERE val_2 = something))
pankajshivnani123
Most of the databases follow the basic syntax,


INSERT INTO TABLE_NAME

SELECT COL1, COL2 ...

FROM TABLE_YOU_NEED_TO_TAKE_FROM;

Login / Signup to Answer the Question.

LoginSignup