Lee Crain (11/6/2012)

---

Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL 
    DROP TABLE #T;
	
CREATE TABLE #T
    (ID INT IDENTITY
            PRIMARY KEY,
     MyColumn1 INT NOT NULL);

INSERT  INTO #T
        (MyColumn1)
VALUES  (1),
        (2),
        (5);

GSquared (11/6/2012)

---

Used to be partially true in SQL 7.5 and before.

Lee Crain (11/6/2012)

---

Jeff Moden,

Please clarify this subject for me:

I'm almost certain that I read somewhere that a "SELECT .... INTO #Table" would automatically create the target table, then lock the TempDB database for the duration of the data insertions.

Is this true or is it incorrect information?

Thanks,

Jeff Moden (11/6/2012)

---

I believe you meant 6.5 and before. SP1 of 6.5 fixed the problem and it's been a built in fix since 7.0. It still puts on some shared locks in TempDB which will make the SSMS GUI give a time out when you right click on tempdb for properties but it's so fast that it doesn't usually matter.

As of 2005, it still has a problem if the source is from a linked server (personal experience... no written proof). We did this once and it caused massive timeouts on the source server. You can try it but be aware of the possible consequences.

I do have a Microsoft link for the 6.5/SP1 thing at home. I'll try to remember to post it when I get hoem.