Insert multiple values using INSERT INTO (SQL Server 2005)

SQL Server 2005: Single vs. Multi-row Insert

Unfortunately, SQL Server 2005 does not support the neat multi-row INSERT like its successor SQL Server 2008. However, we can improvise using either multiple INSERTs or INSERT INTO...SELECT...UNION ALL.

The Old-fashioned way: Single-row Inserts

-- Insert Customer Darth Vader's order...
INSERT INTO Orders (CustomerName, OrderDetail) VALUES ('Darth Vader', '100 Death Stars');
-- Now insert Yoda's order...
INSERT INTO Orders (CustomerName, OrderDetail) VALUES ('Yoda', '1000 Lightsabers');


It inserts one row at a time, perfect for small datasets, but not for a galactic scale operation.

Clever way: Simulating Multi-row Using UNION ALL

-- Inserting both Sith and Jedi orders in one fell swoop!
INSERT INTO Orders (CustomerName, OrderDetail)
SELECT 'Darth Vader', '100 Death Stars'
UNION ALL SELECT 'Yoda', '1000 Lightsabers';


Using UNION ALL simulates multi-row insert, reducing round-trip times to the DB.

Test Run Your Inserts: A Wise Precaution

Before you fire your query thrusters, always test run with a small sample to prevent unwanted Sith (or database) surprises!

Multi-row Insert: Potential Pitfalls and Optimization

When deploying massive inserts into SQL Server 2005, be aware of these stormtrooper traps.

Column-Value Mapping: Mismatch Mayhem

Check your column-value pairings. A string in an integer column is like an Ewok in a Stormtrooper suit – suspicious!

Performance Tuning: Be a Query Jedi

Optimize your INSERT statements. Consider transaction logs and index maintenance. A poorly optimized query is like Jabba the Hutt — bloated and slow!

Syntax Snipers: Hunting Parentheses and Commas

A misplaced parenthesis or comma can bring your INSERT down quicker than the Death Star. Beware of the syntax snipers!

Handling Complex Inserts

Deftly handle advanced scenarios where inserts behave like shifty, shapeshifting bounty hunters.

Conditional Inserts with WHERE

Use a WHERE clause in your SELECT when elaborating a stealthy, condition-based insert:

INSERT INTO Orders (CustomerName, OrderDetail)
SELECT CustomerName, OrderDetail
FROM PotentialOrders
WHERE OrderValue > 10000;
-- Inserts only for customers who aren't cheapskates!


Probing Relationships with JOIN

Execute joins in your INSERTs for inter-table data relations:

INSERT INTO Orders (CustomerName, OrderDetail)
SELECT c.CustomerName, po.OrderDetail
FROM Customers c
JOIN PotentialOrders po ON c.CustomerID = po.CustomerID;
-- Orders placed by patrons already in our customer database. No bantha fodder here!


Visualization

Visualizing bulk data insertion as loading your favorite bookshelf helps. Think of each volume as a new row:

Shelf (📚📚📚) = [Volume 1, Volume 2, Volume 3]

By using multiple value insertion:

INSERT INTO BookShelf (Book) VALUES ('Volume 4'), ('Volume 5'), ('Volume 6');


You expanded your knowledge repository swiftly:

Updated Shelf (📚📚📚📚📚📚): [Volume 1, Volume 2, Volume 3, Volume 4, Volume 5, Volume 6]

Now you have twice the books ready to be read in the same space trip:

**Shelf Before Insertion**: 📚📚📚 (3 Books)
**Shelf After Insertion**: 📚📚📚➕📚📚📚 (6 Books)