How To Insert Multiple Rows with One INSERT Statement in SQL Server?

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives you a good example:

INSERT INTO fyi_links SELECT id+500, REVERSE(url), 
   notes, counts, created FROM fyi_links
GO
(3 row(s) affected)

SELECT * FROM fyi_links
GO
id      url                     notes   counts  created
101     dev.fyicenter.com       NULL    0       2006-04-30
102     dba.fyicenter.com       NULL    0       2007-05-19
103     sqa.fyicenter.com       NULL    NULL    2007-05-19
601     moc.retneciyf.ved       NULL    0       2006-04-30
602     moc.retneciyf.abd       NULL    0       2007-05-19
603     moc.retneciyf.aqs       NULL    NULL    2007-05-19

As you can see, "INSERT INTO ... SELECT ..." is powerful statement. you can use it build up data in tables quickly.

⇒ Updating Values with UPDATE Statements in SQL Server

⇐ Duplicate Key Error on Primary Key Columns in SQL Server

⇑ Using INSERT, UPDATE and DELETE Statements in SQL Server

⇑⇑ SQL Server Database Tutorials