如何批量插入多值？实现1-1000用户ID同余额的插入函数

Hey there! Instead of writing 1000 identical INSERT statements, we can use database-specific features to generate and insert the data in one go. Here are the most efficient methods for common database systems:

You have two solid options here—either a stored procedure with a loop, or a recursive CTE to generate the IDs first:

Option 1: Stored Procedure with Loop #

This is straightforward if you prefer procedural code:

DELIMITER //
CREATE PROCEDURE InsertBulkUsers()
BEGIN
    DECLARE current_id INT DEFAULT 1;
    WHILE current_id <= 1000 DO
        INSERT INTO table_name(user_id, user_balance) VALUES(current_id, 500);
        SET current_id = current_id + 1;
    END WHILE;
END //
DELIMITER ;

-- Execute the procedure to insert all 1000 rows
CALL InsertBulkUsers();

Option 2: Recursive CTE (Faster, Less Overhead) #

This method generates all user IDs in a single query and inserts them at once, which is more efficient than looping:

INSERT INTO table_name(user_id, user_balance)
WITH RECURSIVE id_series AS (
    SELECT 1 AS user_id
    UNION ALL
    SELECT user_id + 1 FROM id_series WHERE user_id < 1000
)
SELECT user_id, 500 FROM id_series;

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

INSERT INTO table_name(user_id, user_balance)
SELECT generate_series(1, 1000), 500;

That's it—one line to generate all 1000 rows and insert them. You can also use a recursive CTE like in MySQL if you prefer, but generate_series is the most concise approach here.

Similar to MySQL, you can use a recursive CTE, but note that SQL Server has a default recursion limit of 100, so we need to adjust that:

INSERT INTO table_name(user_id, user_balance)
WITH id_series AS (
    SELECT 1 AS user_id
    UNION ALL
    SELECT user_id + 1 FROM id_series WHERE user_id < 1000
)
SELECT user_id, 500 FROM id_series
OPTION (MAXRECURSION 1000);

Alternatively, if you don't want to mess with recursion limits, you can generate IDs using system tables:

INSERT INTO table_name(user_id, user_balance)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS user_id, 500
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;

Pro Tip #

Whenever possible, use the set-based approaches (CTEs or generate_series) instead of loops. They're faster because they minimize the number of round-trips between your application and the database, and databases are optimized for set operations.

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