Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Inserting Multiple Rows with One INSERT Statement

By: FYIcenter.com

(Continued from previous topic...)

How To Insert Multiple Rows with One INSERT Statement?

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.

(Continued on next topic...)

1. What Are DML (Data Manipulation Language) Statements?
2. How To Create a Testing Table with Test Data?
3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
4. How To Use Column Default Values in INSERT Statements?
5. How to provide column names in INSERT Statements?
6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
7. How To Insert Multiple Rows with One INSERT Statement?
8. How To Update Values in a Table with UPDATE Statements?
9. How To Update Multiple Rows with One UPDATE Statement?
10. How to use old values to define new values in UPDATE statements?
11. Is the Order of Columns in the SET Clause Important?
12. How To Use Values from Other Tables in UPDATE Statements?
13. What Happens If the UPDATE Subquery Returns No Rows?
14. What Happens If the UPDATE Subquery Returns Multiple Rows?
15. How To Delete an Existing Row with DELETE Statements?
16. How To Delete Multiple Rows with One DELETE Statement?
17. How To Delete All Rows with DELETE Statements?
18. How To Delete All Rows with TRUNCATE TABLE Statement?