A week back i had a performance issue with inserting duplicating records to a table.
Previously i was using cursors to duplicate the records. But cursor sucks a lot. I felt OK when i try to insert around 100 records. But when records are in 1000’s, process was dead slow.
So i decided to tweak the query. After spending many sleepless nights, I found a way to duplicating records without using cursor.
Here is the sample query that illustrate how to insert duplicate records.
INSERT INTO report (id,Name,Col1,Col2)
SELECT id,Name,Col1,Col2 FROM (SELECT 1 AS id
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10) AS o
from [TABLE] WHERE o.id<=NoOfDuplication.