WITH (TABLOCK) - Minimal logging while insert

In order to improve performance for an insert statement,

We can consider use WITH (TABLOCK) option:

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME) SELECT ...

Whet it will do is to minimize the writes to the log.

Try it:

Run insertion statement with and without the TABLOCK and check the log:

INSERT INTO dbo.testItai (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b 

Check the log using

select * from fn_dblog(null, null)

You will see that after the insertion without the TABLOCK 5000 records will be written in the log, and much less with the TABLOCK.

WITH (TABLOCK) has few constraints:

1. The destination table must be empty or without clustered index.

2. There is no non-clustered index on the destination table.

select ... into ... union

declare @a table (aint int)

declare @b table (bint int)

insert into @a (aint) values (1), (2), (3)

insert into @b (bint) values (1), (2), (4), (6)

If you need to insert an union select into a temp table, like this:

select * into #tmp

from (select aint from @a union select bint from @b) t

You can also do it like this:

select aint

into #tmp

from @a

union

select bint

from @b

Check it:

select * from #tmp

(and also drop the temp table :) drop table #tmp )

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

Error message:

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Problem:

As written...  INSERT statement exceeds the maximum allowed number of 1000 row values.

Surprised, but INSERT statement using 'VALUES' (not insert with a select) - have limitation of 1000 inserted records.

Solution:

Anything that will use INSERTs with maximum 1000 records: separate to few INSERTs, insert the new data to temp table and insert using select from the temp table and so on.