Insert into ... values ( SELECT ... FROM ... )

For Microsoft SQL Server, I will recommend learning to interpret the SYNTAX provided on MSDN. With Google it's easier than ever, to look for syntax.

For this particular case, try

> Google: insert site:microsoft.com

The first result will be

[To see links please register here]

scroll down to the example ("Using the SELECT and EXECUTE options to insert data from other tables") if you find it difficult to interpret the syntax given at the top of the page.

[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]
| derived_table <<<<------- Look here ------------------------
| execute_statement <<<<------- Look here ------------------------
| <dml_table_source> <<<<------- Look here ------------------------
| DEFAULT VALUES
}
}
}
[;]

This should be applicable for any other RDBMS available there. There is no point in remembering all the syntax for all products IMO.

To add something in the first answer, when we want only few records from another table (in this example only one):

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES (value1, value2,
(SELECT COLUMN_TABLE2
FROM TABLE2
WHERE COLUMN_TABLE2 like "blabla"),
value4);

INSERT INTO yourtable
SELECT fielda, fieldb, fieldc
FROM donortable;

This works on all DBMS

Simple insertion when table column sequence is known:

Insert into Table1
values(1,2,...)

Simple insertion mentioning column:

Insert into Table1(col2,col4)
values(1,2)

Bulk insertion when number of selected columns of a table(#table2) are equal to insertion table(Table1)

Insert into Table1 {Column sequence}
Select * -- column sequence should be same.
from #table2

Bulk insertion when you want to insert only into desired column of a table(table1):

Insert into Table1 (Column1,Column2 ....Desired Column from Table1)
Select Column1,Column2..desired column from #table2
from #table2

Here's how to insert from multiple tables. This particular example is where you have a mapping table in a many to many scenario:

insert into StudentCourseMap (StudentId, CourseId)
SELECT Student.Id, Course.Id FROM Student, Course
WHERE Student.Name = 'Paddy Murphy' AND Course.Name = 'Basket weaving for beginners'

(I realise matching on the student name might return more than one value but you get the idea. Matching on something other than an Id is necessary when the Id is an Identity column and is unknown.)

You could try this if you want to insert all column using `SELECT * INTO` table.

SELECT *
INTO Table2
FROM Table1;

If you go the INSERT VALUES route to insert multiple rows, make sure to delimit the VALUES into sets using parentheses, so:

INSERT INTO `receiving_table`
(id,
first_name,
last_name)
VALUES
(1002,'Charles','Babbage'),
(1003,'George', 'Boole'),
(1001,'Donald','Chamberlin'),
(1004,'Alan','Turing'),
(1005,'My','Widenius');

Otherwise MySQL objects that "Column count doesn't match value count at row 1", and you end up writing a trivial post when you finally figure out what to do about it.

Best way to insert multiple records from any other tables.



INSERT INTO dbo.Users
( UserID ,
Full_Name ,
Login_Name ,
Password
)
SELECT UserID ,
Full_Name ,
Login_Name ,
Password
FROM Users_Table
(INNER JOIN / LEFT JOIN ...)
(WHERE CONDITION...)
(OTHER CLAUSE)

Instead of `VALUES` part of `INSERT` query, just use `SELECT` query as below.

INSERT INTO table1 ( column1 , 2, 3... )
SELECT col1, 2, 3... FROM table2

INSERT INTO FIRST_TABLE_NAME (COLUMN_NAME)
SELECT COLUMN_NAME
FROM ANOTHER_TABLE_NAME
WHERE CONDITION;