Monday, November 17, 2008

Multi-row INSERT

With SQL, we can SELECT, UPDATE and DELETE multiple rows simultaneously, but we can normally INSERT only one row at a time.

There ways around this, but they usually involve selecting a resultset and passing it to an INSERT statement like this:
INSERT INTO emp (id, ename)
SELECT 4567, 'nitin'
UNION ALL
SELECT 4568, 'naina'

With Microsoft SQL Server 2008, you can now insert multiple values with an INSERT statement. Example:
INSERT INTO emp (id, name) VALUES (4567, 'nitin') (4568, 'naina')

This ought to make help make applications more efficient as they can send multiple values in one go instead having to send data to the server one row at a time.

Will other database engines follow suit and adopt this syntax? Time will tell.

No comments: