We can use a JOIN clause to get data from multiple tables. These tables are joined with conditions specified with the ON clause. Suppose we want to get data from multiple tables and insert into a table.
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
Insert an entire column's data INSERT INTO table_a (col1a) SELECT col1b FROM table_b; That statement will select all data from col1b in table_b and insert into col1a in table_a . You can insert multiple columns from multiple columns: INSERT INTO table_a (col1a, col2a, col3a, …)
The insert statement actually has a syntax for doing just that. It's a lot easier if you specify the column names rather than selecting "*" though:
INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
-- optionally WHERE ...
I'd better clarify this because for some reason this post is getting a few down-votes.
The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. As others have said, the SELECT ... INTO syntax is for when you want to create the new table as part of the command.
You didn't specify whether the new table needs to be created as part of the command, so INSERT INTO ... SELECT FROM should be fine if your destination table already exists.
Try this:
INSERT INTO newTable SELECT * FROM initial_Table
You can insert using a Sub-query as follows:
INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where column=value
From here:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
You can use a select into
statement. See more at W3Schools.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With