Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

My  script for batch insert  running endlessly

Posted on 2014-02-27
2
Medium Priority
?
451 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1000 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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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