Search

Add a feature to allow a minimally logged insert from a SELECT by Adam Machanic

Closed
as Fixed Help for as Fixed

15
0
Sign in
 to vote
Type: Suggestion
ID: 151561
Opened: 6/29/2006 12:22:07 PM
Access Restriction: Public
0
Workaround(s)
Currently, a minimally logged insert can only occur via BULK INSERT, BCP, OPENROWSET(bulk), or SELECT INTO. I'd ilke to be able to do a minimally logged insert into a table with no rows or no indexes, directly from a SELECT. This would be very useful in many cases. For example, it's often necessary to move around data in the same instance of SQL Server, between databases, and oftentimes bulk logging makes a lot of sense -- but SELECT INTO is not always convenient, and exporting the data to a file only to re-insert it is a messy solution at best (and in addition, incurs a lot of the I/Os that we're trying to avoid by using bulk logging in the first place).
Details (expand)
Product Language
English
Version
SQL Server 2005 - Developer Edition (32)
Category
SQL Engine
Operating System
Windows XP SP2 Professional
Operating System Language
English
Proposed Solution
Enable some form of bulk insert via a SELECT. This could be done either by enhancing BULK INSERT, or by enhancing OPENROWSET's bulk option.
Benefits
Faster Development
Improved Performance
Other Benefits
improved inserts
File Attachments
0 attachments
Sign in to post a comment.
Posted by Ralph Kemperdick MSFT on 11/5/2008 at 5:45 AM
It would be even better if we could also parallelize this operation, once the number of rows that need to be copied goes beyond one million. Currently the select into (insert into select from) runs on a single thread. Can easily verified when looking at the query plan for SETELCT * info #table from myTable. This is not suitable for large datasets. Within oracle and Informix we can surpass the buffer manager and write in parallel directly to the target table. Informix call this lightweight append with minimal logging on the base table.
Posted by Microsoft on 5/14/2008 at 9:37 AM
You will have this capability with a heap with no nonclustered index in SQL2008
Posted by Microsoft on 9/27/2007 at 1:45 PM
This is targetted to be available in SQL Server 2008.
Posted by Adam Machanic on 1/16/2007 at 11:09 AM
This has been active for over six months now, with no comment from Microsoft. Anyone tracking this?
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement