Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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   |
+--------------------------------+
share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

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
;
share|improve this answer
    
Well done answer with step by step approach to reach the final statement. –  Jack Sep 21 '12 at 5:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.