Posted by Erland Sommarskog on 02/09/07 23:07

Dmitri (nienna.gaia@gmail.com) writes:
> I have a developer that is playing around with some SQL statements
> using VB.NET. He has a test table in a SQL 2000 database, and he has
> about 2000 generated INSERT statements.
>
> When the 2000 INSERT statements are run in SQL query analyzer, all
> 2000 rows are added to the table. When he tries to send the 2000
> statements to SQL Server through his app., a random number of
> statements do not get executed. But, SQL Profiler shows that each of
> the 2000 statements are getting sent to the server.
>
> I suggested that he add a "GO" statement at the end of the INSERT
> block, but the statement fails when that is sent to the server.
>
> I know that this is not the ideal manner to insert bulk data to the
> system, but now we are all just curious as to why SQL server doesn't
> execute each individual INSERT.

Did he send one batch with 2000 statements, or 2000 batches? Without
seeing the code, it's difficult to know what we are talking about.

The most effective way of inserting data this way is:

INSERT tbl (....)
EXEC('SELECT ''thisvalue'', 1, ''thatvalue''
SELECT ''thisothervalue'', 2, ''thatothervalue''
...')

This keeps it down to one INSERT statement, but many small SELECT
statements that are easy to compile. (The alternative SELECT UNION
is very expensive to compile for 2000 rows.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]