SQL Server Performance Forum – Threads Archive

SELECT INTO…how safe it is?

Hi all,
I am using SELECT INTO query in one of my projects.
But on one of the articles on this website it is written that… "If you need to use the SELECT INTO option, keep in mind that it can lock system tables, preventing others users from accessing the data they need. If you do need to use SELECT INTO, try to schedule it when your SQL Server is less busy, and try to keep the amount of data inserted to a minimum."
Can somebody please throw some light on this. I will be using this query to create a very large table with around 64 million rows. Also, I am getting a real good performance benefit by creating a table dynamically instead of creating a table and then moving the data. Thanks in advance..

---

SELECT INTO acts as a single transaction, and so as the table is being created as well as populated, the system tables (the ones holding the schema of the new table) are locked. Whilst this may not affect performance of the underlying query, it can badly affect concurrency of the entire database, its just as bad (if not worse) when youre using #temp table Instead use CREATE TABLE MyDestTable (id int, field1 varchar, field2 datetime etc)
insert into MyDestTable (id, field1, field2, etc) SELECT id, field1, field2 from MySourceTable
This way the system tables are only locked very briefly during the CREATE TABLE
Then the only lock is the table lock on MyDestTable while the INSERT completes Hope this helps

---

1 The target table wont have indices, constraints , etc that source table has
2 It will lock the source talbe till new table is created
3 Precreate table and do insert as suggested Madhivanan Failing to plan is Planning to fail

---

If I remember well, it was not the case starting from one of the service packs released. However, I am not quite sure about that.

---

]]>