efficient ways to insert multiple rows in SQL

Inserting multiple rows efficiently can depend on your SQL dialect, the volume of data, and whether you're scripting or working in an application context. Here are some broadly effective methods across different scenarios:

1. Multi-row VALUES Clause (Great for small batches)

INSERT INTO chequeIssueRegister (ChequeBookId, ChequeSlNo) VALUES (1, 100001), (1, 100002), (1, 100003);

This is clean, fast for a few hundred rows, and avoids loop overhead.

2. SELECT ... FROM with a Numbers Table or CTE (Scalable & elegant)

If you're generating a sequence:

WITH Numbers AS (

    SELECT 100001 AS ChequeSlNo

    UNION ALL

    SELECT ChequeSlNo + 1 FROM Numbers WHERE ChequeSlNo < 100100

)

INSERT INTO chequeIssueRegister (ChequeBookId, ChequeSlNo)

SELECT 1, ChequeSlNo FROM Numbers

OPTION (MAXRECURSION 0);

This is extremely efficient when inserting contiguous sequences.

3. BULK INSERT or bcp Utility (Perfect for large datasets)

Prepare a .csv or .txt file and then:

BULK INSERT chequeIssueRegister FROM 'C:\Path\To\File.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Much faster than row-by-row for thousands or millions of entries.

4. Table-Valued Parameters (TVPs) in C# apps

If  you're fluent with C#, you can define a DataTable of rows in .NET and pass it to a stored procedure using a TVP. Extremely efficient and keeps your application logic clean.