INSERT INTO SELECT - large amount of records

Question

I want to insert records into a TempTable. Something like this:

insert into ##tempT
SELECT * FROM MyTable 

MyTable contains a large amount of records so the "insert into" takes a long time.

If I try to run:

SELECT COUNT(*) FROM ##tempT

it returns always "0" until all records from "MyTable" are inserted by INSERT INTO command.

How can I get a progress count that advise me how many records are in ##tempT?

I need to update a progress bar value while the SQL command is running.

Thank you.

Answer 1

try

set transaction isolation level read uncommitted
SELECT COUNT(*) FROM ##tempT

Answer 2

You can split your query up.

x = number of records in MyTable / 100
i = 0

do until we're done
    queryString = "insert into ##tempT "
    queryString += "select top " + x " + " * FROM MyTable "
    queryString += "where RecordNumber > " + i

    Execute queryString
    Update Progress Bar
    i = i + x
loop

You'll notice that you'll need some sort of RecordNumber field to make this work though. There are various ways to accomplish that you can search for.