One of the things that has been annoying for a long time in SQL Server is adding lots of data for testing. Personally I use Data Generator, and I recommend that, but for a quick few rows of data, do you want to do this:

CREATE TABLE SalesOrders
( OrderID INT IDENTITY(1,1)
, OrderDate DATETIME
, CustomerID INT
, OrderAmount NUMERIC(10, 4)
)
GO
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

There’s a lot of INSERT typing, even with copy/paste functionality and editing the various rows gets to be cumbersome of you actually get some sample data like this that you want to convert (say from someone’s blog post of results:

1, 12/1/2011, 3, 123

2, 12/2/2011, 4, 2

3, 12/1/2011, 3, 123

You could do the UNION thing, like this:

INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
UNION ALL
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
UNION ALL
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
UNION ALL
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
UNION ALL
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

That works, but it’s still a little cumbersome.

In SQL Server 2008, there’s a better way. You can now include multiple sets of data in your insert, like this:

INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
VALUES
(           '1982-05-19 06:31:48.950', 1,           579040.5070),
(           '1994-11-27 17:14:41.790', 2,           348808.5860),
(           '1972-11-08 17:40:01.170', 3,           758992.3650),
(           '1972-05-31 01:19:05.530', 4,           779853.1990),
(           '1994-12-22 10:40:57.410', 5,           666173.8040)

Just put brackets around each set of data, and you can easily insert multiple rows.

Filed under: Blog Tagged: sql server, syndicated, T-SQL