Query to insert and select by batches

motioneye
motioneye used Ask the Experts™
on
I have write the query below, but I have a doubt that will this query run with inserting only new records by batches or  will it run endlessly.

DECLARE @i INT
WHILE 1 = 1 BEGIN
     BEGIN TRANSACTION
      Insert into SynState select  TOP(10000) * --into SyncExchangeStateData
      FROM SyncSatteTrue with (nolock)

      SET @i = @@ROWCOUNT
      commit transaction
      IF @i = 0 BREAK
     
END
GO
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
it will run endlessly, as you don't specify anywhere which rows to "skip" on next run, and you don't delete any rows neither...
Valliappan ANSenior Tech Consultant

Commented:
As Guy Hengel stated, it will run endlessly, if you want to do  it in batches, have a look at:

http://sqlserverplanet.com/data-warehouse/transferring-large-amounts-of-data-using-batch-inserts
(Transferring Large Amounts of Data using Batch Inserts)

Author

Commented:
Thanks, Now I fix the script :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial