Is it possible to generate table with generated data without manual INSERT statements?

Question

Is it possible to use SQLCMD in SQL Server 2008 to create a table and generate data and insert into the table, without manually typing the INSERT statement for each row?

Example:

Suppose there are 10 weeks (1, 2, 3 ... 10) and 2 timings (11 am, 12 pm) and 5 venues (Room 1, Room 2 ... Room 5). Thus there will be a total of 10 x 2 x 5 = 100 rows.

So, is it possible to have a SQL statement that will generate these 100 rows when given the 3 data parameters (week, timing and venue)?

+--------------------------------+
| Week    |   Time   |   Venue   |
+--------------------------------+
| 1       |  11 am   |  Room 1   |
| 1       |  12 pm   |  Room 1   |
| 1       |  11 am   |  Room 1   |
|                                |
| 10      |  12 pm   |  Room 5   |
+--------------------------------+

Answer 1

You can use 3 CTE expressions to generate the data, then cartesian product them together.

Test table:

create table calendar
(
week integer,
time varchar(20),
venue varchar(20)
);

Weeks:

SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10;

Venues:

SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5;

Times:

SELECT '11 am' AS t
UNION
SELECT '12 pm' AS t;

Put them all together to make a test query:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;

Convert to an INSERT statement:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
INSERT INTO calendar (week, time, venue)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;