Solved

My  script for batch insert  running endlessly

Posted on 2014-02-27
2
367 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access join syntax when converting to T-SQL query 4 36
Negative isnull? 3 16
SQL Dump exec output to table 3 22
MS SQL Delete Duplicate Rows Only 2 20
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 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