Grouping values for INSERTion into table

Question

I'd like to be able to insert some values into a table using a single INSERT statement, but I'm having trouble determining the best way. The data I'm inserting is bootstrap (or hard-coded) data, ie. not available in another table. Here's what I've tried so far.

create table #t (id int)

insert into #t values (1)  --- (a)
insert into #t values (5)  --- (a)

insert into #t values ((5), (3), (22))  --- (b)

insert into #t             --- (c)
select 3 union all
select 5 union all
select 6 union all
select 8

The code at (a) works just fine, but adds only a single value per insert.

The code at (b) seems to be the logical extension of (a) to me (at least when I think of initialisers from Perl or Java), but doesn't work.

The code at (c) works and is probably more of the SQL way, but seems verbose.

Is there a variant of (b) that works? Or is there an approach like (c) that requires less typing?

I'm using SQL Server 2000, but I'm interested in something that's fairly portable if possible.

UPDATE: Thanks to everynoe who answered. In summary, it seems the SQL standard has a multi-insert, which is what I was after, but is not supported in my environment. So I will stick with (a) for simplicity and clarity.

Answer 1

Is there a variant of (b) that works?

INSERT can only insert one row at a time. If you'd like to insert more than one row, you'll have to call it many times. So in short, no.

Or is there an approach like (c) that requires less typing?

There might be a short-handed way to perform the unions, but you should be aware that performing all of those unions will require a lot of memory and be significantly slower than calling INSERT multiple times.

If your data is hard-coded and you're looking to bulk import you might want to look into BULK INSERT. You can use BULK INSERT to read CSV data.

Answer 2

Well, you could insert into a temporary table and then do the following:

INSERT INTO ...
SELECT FROM ...

Answer 3

The SQL standard provides for multiple lists of values, each in its own set of parentheses, which is a minor variant on your (b):

INSERT INTO "#t" VALUES (1), (2), (3);

Not all DBMS support this notation, though, and I'd be surprised if SQL Server 2000 did.

If the code is 'setup' code (as the question implies) that is executed once and not redone, then worrying about the utmost in efficiency is usually a premature optimization. Make it simple and clear. Use mechanism (a).