How can I insert more than 1,000 rows in SQL Server?

 Inserting more than 1,000 rows into SQL Server can be done in several ways, depending on the tools and methods you have available. Below are common approaches:

---

1. Use INSERT INTO with Multiple Values

You can include multiple rows in a single INSERT INTO statement, but SQL Server limits the number of rows per statement to 1,000 when using this syntax. If you have more than 1,000 rows, you need to split them into multiple INSERT statements.

INSERT INTO YourTable (Column1, Column2, Column3)
VALUES
    (Value1, Value2, Value3),
    (Value4, Value5, Value6),
    -- Add up to 1,000 rows
    (Value999, Value1000, Value1001);

---

2. Use a Table-Valued Parameter (TVP)

Table-Valued Parameters are a more efficient and flexible way to handle bulk inserts. You define a user-defined table type and pass the data to a stored procedure.

Example:

1. Create a user-defined table type:

   CREATE TYPE MyTableType AS TABLE
   (
       Column1 INT,
       Column2 NVARCHAR(50),
       Column3 DATETIME
   );
   

2. Create a stored procedure to accept the TVP:

   CREATE PROCEDURE InsertMyTable
   @MyTable MyTableType READONLY
   AS
   BEGIN
       INSERT INTO YourTable (Column1, Column2, Column3)
       SELECT Column1, Column2, Column3
       FROM @MyTable;
   END;
   

3. Pass data from your application using the TVP.

---

3. Use BULK INSERT

If your data is in a file (e.g., CSV), you can use the BULK INSERT command to import it directly into the database.

BULK INSERT YourTable
FROM 'C:\Path\To\File.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2 -- Skip the header row if needed
);

---

4. Use SQL Server Integration Services (SSIS)

For large-scale data imports, you can use SSIS, a powerful ETL tool included with SQL Server. SSIS allows you to design data workflows to import data from various sources.

---

5. Use OPENROWSET or BULK with a Format File

This is an alternative to BULK INSERT where you can use a format file for more complex imports.

INSERT INTO YourTable
SELECT *
FROM OPENROWSET(
    BULK 'C:\Path\To\File.csv',
    FORMATFILE = 'C:\Path\To\FormatFile.fmt'
) AS TempTable;

---

6. Use ORM or Custom Application Code

If you're working with an application, most ORMs (e.g., Entity Framework, Hibernate) support bulk inserts through batch operations. Alternatively, you can write a script to generate INSERT statements programmatically.

---

7. Use MERGE Statement

If you need to insert or update data conditionally, you can use the MERGE statement.

MERGE INTO YourTable AS Target
USING (VALUES 
    (Value1, Value2, Value3),
    (Value4, Value5, Value6)
    -- Add more rows
) AS Source (Column1, Column2, Column3)
ON Target.Column1 = Source.Column1
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Column1, Column2, Column3)
    VALUES (Source.Column1, Source.Column2, Source.Column3);

---

Best Practice

For inserting a large number of rows, consider BULK INSERT, SSIS, or Table-Valued Parameters, as they are optimized for handling large datasets efficiently.