How to insert duplicate records in sql without using cursor

Hello world,

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *