Faster Insert?

I was wondering which was faster:

INSERT INTO tab1 (col1, col2, col3) SELECT col1, col2, col3 FROM tab2

or

SELECT col1, col2, col3 INTO tab1 FROM tab2

I am inserting 400,000 rows and I am trying to figure out what the fastest way to do that is.

Thanks for any advice / help in advance

The Select Into has to create the metadata and the table, so that is some overhead.

But not enough that you'd even notice

If your recovery model is Bulk Logged, and depending on indexes, et al. you might be able to get minimal logging with a locking hint on the INSERT INTO, which would speed things up some.

Having your query optimized is probably the most important first step.

|||

they don't make much difference... however, having an index on those columns will be very costly when inserting. it maybe worth dropping and recreating after the insert...

just a thought.

|||First one is always fully logged except for INSERT...SELECT OPENROWSET(BULK) in SQL Server 2005. The other operation is minimally logged in simple or bulk_logged recovery model. The fastest way to create a table with data is to do SELECT...INTO. But both these statements are not identical in functionality. The 2nd one will fail if the table exists (tab1 in your example). And they have different permission requirements too.|||

DaleJ wrote:

If your recovery model is Bulk Logged, and depending on indexes, et al. you might be able to get minimal logging with a locking hint on the INSERT INTO, which would speed things up some.

INSERT is always fully logged except when used with OPENROWSET(BULK) in SQL Server 2005. There is no way to get minimal logging for other INSERT statements in SQL Server.