Using SQL to insert all rows from a select

JustinDatabases, MySQL, SQL Server SQL

Inserting all rows from a select statement is a fairly common operation. You may be transferring the contents of one table to another or performing a complex multi-joined select and moving those values to another temporary table. The basic statement goes something like this:

INSERT INTO table1 (column1)
SELECT col1
FROM table2

You can modify this for your specific purposes like so:

INSERT INTO AccessLog (UserId, PageId, AccessTime)
SELECT UserId, 5, now()
FROM Users