如何在SQL Server中将表中所有记录重复n次？

Absolutely! You don’t need to manually copy-paste anything—you can achieve this by combining your table with a generated list of numbers (from 1 to n) using a cross join. Each row in your original table will pair with every number in the list, resulting in n copies of each record. Here’s how to do it across common SQL databases:

PostgreSQL #

PostgreSQL has a built-in generate_series function that makes this straightforward:

SELECT t.*
FROM table1 t
CROSS JOIN generate_series(1, 5); -- Replace 5 with your desired number of repetitions

MySQL (8.0+) #

Use a recursive CTE to generate the number series (CTEs were added in MySQL 8.0):

WITH RECURSIVE nums AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 5 -- Replace 5 here
)
SELECT t.*
FROM table1 t
CROSS JOIN nums;

For older MySQL versions without CTE support, you can create a temporary numbers table or use a union like SELECT 1 UNION SELECT 2 ... (not ideal for large n, but works for small values).

SQL Server #

Recursive CTEs work here too, and you’ll need to add OPTION (MAXRECURSION 0) if n is larger than 100 (the default recursion limit):

WITH nums AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 5 -- Replace 5
)
SELECT t.*
FROM table1 t
CROSS JOIN nums
OPTION (MAXRECURSION 0);

If you’re using SQL Server 2022 or later, you can also use GENERATE_SERIES just like PostgreSQL:

SELECT t.*
FROM table1 t
CROSS JOIN GENERATE_SERIES(1, 5);

SQLite #

SQLite supports recursive CTEs for generating the number list:

WITH RECURSIVE nums(num) AS (
    SELECT 1
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 5 -- Replace 5
)
SELECT t.*
FROM table1 t
CROSS JOIN nums;

Inserting Duplicates into the Same Table #

If you want to add these duplicates to your original table (instead of just selecting them), wrap the query in an INSERT statement. Just remember to exclude auto-incrementing primary keys (or let the database generate new ones):

-- Example for PostgreSQL
INSERT INTO table1 (column1, column2, column3) -- List all columns except the primary key
SELECT column1, column2, column3
FROM table1 t
CROSS JOIN generate_series(1, 4); -- Use n-1 here since original records already exist

内容的提问来源于stack exchange，提问作者Hassan