SELECT INTO does place locks on system metadata, which can cause issues for users, until the SELECT INTO completes. INSERT INTO can be minimally logged by using a TABLOCK. ~Boyd 🙂
Enhancements in SQL Server 2008
SQL Server 2008 introduces a few important enhancements regarding minimally logged insert methods. The regular INSERT SELECT statement (as opposed to using the BULK rowset provider) can now also be handled with minimal logging. This improvement is important for two reasons. First, you can fully control the target table’s \\[schema\\] definition. Second, unless there are logical reasons for you to put the CREATE TABLE and INSERT SELECT statements in the same transaction, you can run the two in separate transactions. The CREATE TABLE statement will finish very quickly and release all locks on metadata, preventing the problem I described earlier with the SELECT INTO statement. So in SQL Server 2008, a best practice is to use CREATE TABLE with INSERT SELECT instead of SELECT INTO.
For example, instead of
SELECT x, y, z INTO TargetTable FROM SourceTable;
CREATE TABLE TargetTable(x …, y …, z …);
INSERT INTO TargetTable WITH (TABLOCK) (x, y, z)
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]