SELECT INTO is wrapped within a transaction. Tables created by using SELECT INTO hold to the ACID (atomicity, consistency, isolation, durability) transaction properties. This also means that system resources, such as pages, extents, and locks, are held for the duration of the SELECT INTO statement. With larger system objects, internal tasks might be blocked by other users performing SELECT INTO statements. For example, on high-activity servers, if many users run SQL Enterprise Manager to monitor system processes, they might block each other, causing a condition in which SQL Enterprise Manager appears to hang.
Upgrade to SQL Server 6.5 Service Pack 2, to ensure that you can set the SELECT INTO locking feature as you desire. Wrapping the SELECT INTO with a transaction remains the default behavior. For users wishing not to hold system catalog locks on the activity, a trace flag has been added to allow for such operations. To apply the trace flag, start the server with the -T5302 command-prompt parameter, or from within a query window, use the following commands:
dbcc traceon (3604) go dbcc traceon (5302) go
When the 5302 trace flag is applied and a SELECT INTO fails, the table is still created. The locking behavior you select is applied for all databases within the server.
SELECT INTO is not an atomic operation.