Inserting Multiple Rows with One INSERT Statement in SQL Server

Q

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

✍: FYIcenter.com

A

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

2016-11-02, 1472🔥, 0💬