"select into" vs "insert into" question

Hello,

I have read in a number of forums that when using temporary tables, it is best to create the tables first before inserting the records rather than simply executing a "select into" to ensure that locking issues are alleviated.

I have to say that I am one of those developers who has relied on using the "select into" statement - the main reason being that I have almost always found the "select into" statement to be much faster than an "insert into" statement.

Obviously, the number of users running queries simultaneously when scripts that use "select into" statements are executed is a big deciding factor on whether "select into" or "insert into" should be used.

I'd appreciate it if someone can shed more light on other factors that should be taken into consideration when deciding to use "select into" vs "insert into".

Thanks in advance for your time and reply.It used to be that SELECT INTO was a no-no because it was an unlogged transaction, after which database recoverability was questionable. With SQL Server 2000 I do not believe this is still an issue, although I have never found documentation clarifying the matter.

INSERT INTO is slower partly because it verifies unique keys and other data restraints as the data is entered. SELECT INTO does not do this, but if you are selecting from a table that already has these restrictions inforced then it is not an issue.

Also, in many cases a table variable is the fastest method to use. Check into them and see if they might be more appropriate for your application.