One thing so consider is whether or not to run a bunch a separate single count queries, as you have proposed, or to run a single query with a number...

Wow. That is some plain ugly looking TSQL.

Having said that, I would have done exactly what you did, if for no other reason than to sustain my own sanity,...

Writing the queries is only half of the equation. The other half (and arguably the more important half) is the database design itself.

All I can say is that if you don't want to normalize it you will have to live with the performance issues.

This data is not normalized, and it will end up being very FAT & containing a lot of repetitive bloated data that will impact your performance when you get 700K...

I am not aware of any "handy" tool to exactly what you want.

If it were me I would write a little .net utility to read the file and write it...

I would suggest that you create a temp table (in tempdb) for this as workaround.

You will still have the create table permission issue in tempdb but what have done is...

several comments here:

@@ERROR is only correct for the statement immediately preceeding it so is not reflecting the status of the actual delete.

I think try catch is a superior way to...

Only way to be sure is to write a CLR (c#) version and test it.

I wrote a T/SQL function to convert an (varchar) IP address to a bigint and compared...

In you specific case, I think this methodology would work:

set @sqlInsert = 'SELECT @ResultCountOut= COUNT(*) FROM ' + @tblResultName

EXEC sp_executesql

@sqlInsert,

...

How I typically deal with this problem is to generate a temp table name based upon something unique to the process. In a typical case you can use the PID...

I don't think you can draw anything conclusive about DOP based on how many threads you see executing. Even with One CPU a query can be handled with multiple SQL...

that is not the process doing the count(*) that is your sp_who2 process.

to avoid confustion. USE MASTER then run the sp_who2 ... look only for results concerning...

You may not have seen my edits:

The reason a select * can return almost immediately is that it doesn't have to read the entire table (all data pages) before returning...

how many rows are in the table? (I mean approximately?)

The reason a select * can return almost immediately is that it doesn't have to read the entire table...