Solved

My  script for batch insert  running endlessly

Posted on 2014-02-27
2
353 Views
Last Modified: 2014-03-11
I have a script below which I need to run them to batch insert new data into another table.
I just don't understands why the script below still repeating doing insert on existing data. I manage to spot teh repetitive insert   by creating a unique index on ID column in table SyncState3Backups which the original column has that as well.



DECLARE @BatchSize INT = 100000

WHILE 1 = 1
BEGIN

    INSERT INTO SyncState3Backups
    (
     Id,
     UserConfigId,
     EntryID,
     Type,
     Folder,
     ChangeKey,
     LastCmdSent,
     LastCmdIDSent,
     LastCmdRcvd,
     LastCmdIDRcvd,
     RecordHash,
     Lurnum,
     FolderId,
     UID  
    )
    SELECT TOP(@BatchSize)
   
    Id,
    UserConfigId,
    EntryID,
    Type,
    Folder,
    ChangeKey,
    LastCmdSent,
    LastCmdIDSent,
    LastCmdRcvd,
    LastCmdIDRcvd,
    RecordHash,
    Lurnum,
    FolderId,
    UID
 
    FROM [dbo].[SyncState] with (nolock) where id <=6365386
   

    IF @@ROWCOUNT < @BatchSize BREAK
   
END
0
Comment
Question by:motioneye
2 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 250 total points
ID: 39891658
I think what's happening here is that your break statement is breaking from the IF statement and back into the while loop.

Why do things this way?

Do something like:

DECLARE @BatchSize INT = 100000, @bBreakBit bit
select @bBreakBit = 0

WHILE @bBreakBit = 0
BEGIN

    INSERT INTO SyncState3Backups
    (
     Id,
     UserConfigId,
     EntryID,
     Type,
     Folder,
     ChangeKey,
     LastCmdSent,
     LastCmdIDSent,
     LastCmdRcvd,
     LastCmdIDRcvd,
     RecordHash,
     Lurnum,
     FolderId,
     UID  
    )
    SELECT TOP(@BatchSize)
   
    Id,
    UserConfigId,
    EntryID,
    Type,
    Folder,
    ChangeKey,
    LastCmdSent,
    LastCmdIDSent,
    LastCmdRcvd,
    LastCmdIDRcvd,
    RecordHash,
    Lurnum,
    FolderId,
    UID
 
    FROM [dbo].[SyncState] with (nolock) where id <=6365386
   

    IF @@ROWCOUNT < @BatchSize select @bBreakBit = 1
   
END 

Open in new window

0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 250 total points
ID: 39891791
Your query is not determining which records have already been inserted, so you are inserting the same 100,000 rows over-and-over.  Assuming ID is the primary-key:
DECLARE @BatchSize INT = 100000

WHILE 1 = 1
BEGIN
	INSERT SyncState3Backups
	(	Id
	,	UserConfigId
	,	EntryID
	,	Type
	,	Folder
	,	ChangeKey
	,	LastCmdSent
	,	LastCmdIDSent
	,	LastCmdRcvd
	,	LastCmdIDRcvd
	,	RecordHash
	,	Lurnum
	,	FolderId
	,	UID   
	)
	SELECT TOP(@BatchSize)
		a.Id
	,	a.UserConfigId
	,	a.EntryID
	,	a.Type
	,	a.Folder
	,	a.ChangeKey
	,	a.LastCmdSent
	,	a.LastCmdIDSent
	,	a.LastCmdRcvd
	,	a.LastCmdIDRcvd
	,	a.RecordHash
	,	a.Lurnum
	,	a.FolderId
	,	a.UID
	FROM	SyncState		a
	LEFT
	JOIN	SyncState3Backups	b	ON	a.Id = b.Id
	WHERE	b.Id IS NULL
	AND	a.id <= 6365386

	IF @@ROWCOUNT < @BatchSize BREAK

END /*while*/

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now