Building a temp table /map

Question

I'm working on a stored procedure in SQL Server 2000 with a temp table defined like this:

CREATE TABLE #MapTable (Category varchar(40), Code char(5))

After creating the table I want to insert some standard records (which will then be supplemented dynamically in the procedure). Each category (about 10) will have several codes (typically 3-5), and I'd like to express the insert operation for each category in one statement.

Any idea how to do that?

The best idea I've had so far is to keep a real table in the db as a template, but I'd really like to avoid that if possible. The database where this will live is a snapshot of a mainframe system, such that the entire database is blown away every night and re-created in a batch process- stored procedures are re-loaded from source control at the end of the process.

The issue I'm trying to solve isn't so much keeping it to one statement as it is trying to avoid re-typing the category name over and over.

Answer 1

This might work for you:

CREATE TABLE #MapTable (Category varchar(40), Code char(5))

INSERT INTO #MapTable 
SELECT X.Category, X.Code FROM
(SELECT 'Foo' as Category, 'AAAAA' as Code
UNION
SELECT 'Foo' as Category, 'BBBBB' as Code
UNION
SELECT 'Foo' as Category, 'CCCCC' as Code) AS X

SELECT * FROM #MapTable

Answer 2

DJ's is a fine solution but could be simplified (see below).

Why does it need to be a single statement?

What's wrong with:

insert into #MapTable (category,code) values ('Foo','AAAAA')
insert into #MapTable (category,code) values ('Foo','BBBBB')
insert into #MapTable (category,code) values ('Foo','CCCCC')
insert into #MapTable (category,code) values ('Bar','AAAAA')

For me this is much easier to read and maintain.

---

Simplified DJ solution:

CREATE TABLE #MapTable (Category varchar(40), Code char(5))

INSERT INTO #MapTable (Category, Code)
SELECT 'Foo', 'AAAAA'
UNION
SELECT 'Foo', 'BBBBB'
UNION
SELECT 'Foo', 'CCCCC' 

SELECT * FROM #MapTable

There's nothing really wrong with DJ's, it just felt overly complex to me.

---

From the OP:

The issue I'm trying to solve isn't so much keeping it to one statement as it is trying to avoid re-typing the category name over and over.

I feel your pain -- I try to find shortcuts like this too and realize that by the time I solve the problem, I could have typed it long hand.

If I have a lot of repetitive data to input, I'll sometimes use Excel to generate the insert codes for me. Put the Category in one column and the Code in another; use all of the helpful copying techniques to do the hard work

then

="insert into #MapTable (category,code) values ('"&A1&"','"&B1&"')"

in a third row and I've generated my inserts

Of course, all of this is assuming that the Categories and Codes can't be pulled from a system table.

Answer 3

insert into #maptable (category, code)
select 'foo1', b.bar
from  
    ( select 'bar11' as bar
     union select 'bar12'
     union select 'bar13'
    ) b
union
select 'foo2', b.bar
from  
    ( select 'bar21' as bar
     union select 'bar22'
     union select 'bar23'
    ) b

Answer 4

Here's the notation I ended up using. It's based on Arvo's answer, but a little shorter and uses cAse to help make things clearer:

SELECT 'foo1', b.code
FROM ( select 'bar11' as code
 union select 'bar12'
 union select 'bar13' ) b

UNION SELECT 'foo2', b.code
FROM ( select 'bar21' as code
 union select 'bar22' 
 union select 'bar32' ) b

This way highlights the category names a little, lines up codes vertically, and uses less vertical space.