• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

My script for batch insert running endlessly

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
motioneye
Asked:
motioneye
2 Solutions
 
Lee SavidgeCommented:
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
 
John_VidmarCommented:
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

1

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now