Well, there are actually 3 ways to do it, not 2.

1 - CREATE TABLE AS SELECT...

2 - CREATE TABLE
INSERT..SELECT

3 - CREATE TABLE
INSERT..VALUES
INSERT..VALUES
...

It sounds as if you are talking about comparing 1 to 3. Actually, let's compare 2 to 3. The only difference there is a single bulk statement (INSERT..SELECT) versus 1000 small statements (INSERT..VALUES). Now, which do you think is faster, a single statement on a set or 1000 statements on rows? Think about it...

Actually, if you have to think about it, you may need a new line of work. Databases are set-oriented beasts, plain and simple. It is always (read:99.9%) better to do a single large statement than lots of little statements. Time it. Check the resources. Total them up. I guarantee you that the single statement will win on all fronts.

So, since 2 is obviously way better than three, the question is actually whether 1 or 2 is the better choice. At the timing and resource level, I would venture to guess that thay are likely equivalent with choice 1 probably slightly ahead. (I'm sure someone will jump right in and give some exact numbers) I also like choice 1 because the datatypes are determined automatically, so there is less work up front and less maintenance headaches down the line.

There may be other implications of which I am unaware.

- Chris