CODEWITHSUNDEEP
sqlsql-servert-sqlinsert
epi82
epi82

Reputation: 497

INSERT INTO SELECT - large amount of records

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.

Upvotes: 5

Views: 3893

Answers (3)

Milee
Milee

Reputation: 1221

Use a stored procedure and DECLARE a variable COUNT and treat this as a looping variable and each time an insert is done, increment COUNT by 1 and keep printing it using another query whenever you want to know the count .Or, return this count from the procedure and read it into your program for updating the progress bar. :)

Upvotes: 1

Diego
Diego

Reputation: 36146

try 

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

Upvotes: 8

Brandon Moore
Brandon Moore

Reputation: 8780

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.

Upvotes: 1

Related Questions