Non-logged insert... select

Hi,

Is there any way in either sql2k or 2005 to do an "insert ... select" without writing to the tran log, similar to a non-logged bcp from a flat file? It seems crazy that I should have to go outside the engine and back in again just to avoid this logging. Or am I mistaken, and this is already what happens when you do an insert... select when the db is in bulk logged or simple mode?

Thanks!

Mike

Hey Mike. You can make use of the 'select...into' statement to perform this type of operation in bulk, of course that means the target table can't already exist. There is no way to perform an 'insert' statement with minimal logging. The only options you have for performing minimal logged operations in SQL 2000 are the following commands:

- BULK INSERT
- bcp
- select...into
- create index
- text and image operations (updatetext and writetext)

There are also prerequisites for minimally logged operations, including:

- Table not being replicated
- Table level locking is specified/used
- Specific index considerations (different depending on types of indexes present, whether or not data exists in the table, etc...see BOL for more information)

In SQL 2005, you have the option of using any of the above, and also the new 'BULK' option with the OPENROWSET command to load data from a text file using minimal logging. I suspect this may be your best option if you are running SQL 2005 and loading data into an existing table. The prerequisites for bulk loading still apply though. For more information on this new 2005 bulk operation, see BOL.

Also in SQL 2005, you could use one of the bulk load commands to bulk load data into a new table using minimal logging, then switch that table's data into an existing table if you make use of table partitioning correctly...the switch into the existing table's partition scheme could be a meta-data only operation if you design the partitioning strategy correctly. See SQL 2005 BOL for more information on table partitioning, or just post another question here and we'll see what we can get you for pointers.

HTH,

|||

Can I use the select...into and try to partition the table?

Such as:

select top(1000)*

into dbo.create_on_the_fly_tbl

from dbo.any_existing_tbl

on partitionrangeSCM(id)