Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ - Inserting Multiple Rows with One INSERT Statement
(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
(3 row(s) affected)
SELECT * FROM fyi_links
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...)
- What Are DML (Data Manipulation Language) Statements?
- How To Create a Testing Table with Test Data?
- How To Insert a New Row into a Table with "INSERT INTO" Statements?
- How To Use Column Default Values in INSERT Statements?
- How to provide column names in INSERT Statements?
- What Happens If You Insert a Duplicate Key for the Primary Key Column?
- How To Insert Multiple Rows with One INSERT Statement?
- How To Update Values in a Table with UPDATE Statements?
- How To Update Multiple Rows with One UPDATE Statement?
- How to use old values to define new values in UPDATE statements?
- Is the Order of Columns in the SET Clause Important?
- How To Use Values from Other Tables in UPDATE Statements?
- What Happens If the UPDATE Subquery Returns No Rows?
- What Happens If the UPDATE Subquery Returns Multiple Rows?
- How To Delete an Existing Row with DELETE Statements?
- How To Delete Multiple Rows with One DELETE Statement?
- How To Delete All Rows with DELETE Statements?
- How To Delete All Rows with TRUNCATE TABLE Statement?