How to duplicate all records in a table SQL Server for n times?-CodePudding

Is there any SQL query to duplicate all records in a table for n times? So lets say I have 1000 record in table1, Is there any SQL query to duplicate these 1000 record for n times? Instead of copy and pasting the data from the beginning?

CodePudding user response：

jarlh and Larnu have given the solution in the request comments, but you are having difficulties understanding the concept.

The duplicates you are talking about are not real duplicates obviously. They differ in their IDs. This means you must list the columns and omit the ID: insert into t (col1, col2) select col1, col2 from t.

With an ad-hoc tally table:

insert into t (col1, col2)
  select col1, col2
  from t
  cross join (values (1),(2),(3),(4),(5)) tally(i);

CodePudding user response：

    DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
    --Do insert here
    SET @Counter  = @Counter    1
END