Something changed on my VM, but not sure what because I didn't change anything. All of a sudden this is the normal results:

UNION method:

Elapsed Time (ms) 2665

UNPIVOT method:...

Set up a quick test here as well:

UNION method:

Elapsed Time (ms) 1339

UNPIVOT method:

Elapsed Time (ms) 1181

Ran it several times with similar results. Sorry, didn't set up a...

Just a quick test using 6 million rows:

Lynn's method:

Elapsed Time (ms) 8276

ScottPletcher's first method:

Warning: Null value is eliminated by an aggregate or other SET operation.

Elapsed Time (ms) 12244

ScottPletcher's...

Now, based on the data above, what is the expected output?

ChrisM@Work (9/11/2012)

---

Lynn Pettis (9/11/2012)

---

ChrisM@Work (9/11/2012)

---

oscarooko (9/11/2012)

---

It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and...

jralston88 (9/11/2012)

---

Attempting to resolve Errorlog file size problem via the SP_Cycle_Errorlog. I've expanded the number of Errorlogs files from 6 to 18. I've also created a job with...

chrismat (9/11/2012)

---

If you copy/paste my post into SSMS and run it, the last active result set contains some question marks....

In Object Explorer, right click on the database, select Tasks, then Generate Scripts, then follow along with the wizard.

ChrisM@Work (9/11/2012)

---

oscarooko (9/11/2012)

---

It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one of you...

You might want to put a DELAY in the TRY part of the code after the commit to allow other activities to access the table from which you are deleting...

You could try using something like this:

declare @BatchSize int = 10000;

while @BatchSize <> 0

begin

begin try

begin transaction

...

You may be deleting in batches, but the whole thing is one big transaction.

Would help to see the stored procedure code and how you are actually calling it. Just based on your initial post, hard to tell you what is happening.

ChrisM@Work (9/11/2012)

---

Lynn Pettis (9/11/2012)

---

ChrisM@Work (9/11/2012)

---

oscarooko (9/11/2012)

---

I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long....

ChrisM@Work (9/11/2012)

---

oscarooko (9/11/2012)

---

I am trying to delete old records from a table, and insert them into another table. Problem is that it is taking too long. The table has...