Fastest way to insert 30 thousand rows in SQL Server

I have to insert 30000 ids in a temporary table ##MyTempTable that has only one column (ID int) and I would like to know which way I could do it fastest.

I have tried 30000 normal inserts

insert into ##MyTempTable (ID) ...

I have tried

insert into ##MyTempTable (ID) (
 select 1002000 union all 
 select 1002001 union all 
 select 1002002 ...

The fastest way so far has been:

insert into ##MyTempTable (ID)
values
(1002000),(1002001),(1002002),(1002003) ...

Because this type of insert only accepts a maximum of one thousand rows, so then I repeated that insert 30 times.

Would you be able to help me?

If you are really just trying to insert 30000 ID values that increment by 1, starting at 1002000, I highly doubt you'll be able to rig up anything in C# that will be faster than something like this:

INSERT ##MyTable(ID)
  SELECT 1001999 + n FROM
  (
    SELECT TOP (30000) n = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]
  ) AS x;

In my lowly Windows VM on a Mac host this occurs sub-second.

For other ideas about generating sets / sequences without looping (e.g. a numbers table may be even faster), see this blog series:

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3

If the values are defined and not some incremental sequence (e.g. 30,000 values you somehow coerced into a DataTable), then you can consider using a table-valued parameter. First, create this type in your database (you just do this once):

CREATE TYPE dbo.MyIDs AS TABLE(ID INT PRIMARY KEY);

Now have a stored procedure that accepts this:

CREATE PROCEDURE dbo.TakeMyIDs
  @m dbo.MyIDs READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT ##MyTable(ID) SELECT ID FROM @m;
END
GO

Now call your stored procedure from C# and pass the DataTable as a parameter:

// assuming an active connection object, conn,
// and a populated DataTable called dtIDs:

SqlCommand cmd = new SqlCommand("dbo.TakeMyIDs", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvp1 = cmd.Parameters.AddWithValue("@m", dtIDs);
tvp1.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
cmd.Dispose();


StackExchange Database Administrators Q#44217, answer score: 5