5

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?

Improve this question
7
  • What programming language? There is a bulk API, but any advice depends - on the environment. C# has a SqlBulkCopy class for that stuff, for example.
    – TomTom
    Jun 10 '13 at 15:34
  • @TomTom - I need to do it in C#. I tried using SqlBulkCopy, and it was much slower. I suppose I didn't use it very well. I tried to create a DataRow for each ID and this loop took ages in comparison to the pure SQL statements. Is there a better way to do this?
    – Jenninha
    Jun 10 '13 at 15:37
  • What about not using a data row at all. We do SqlBulkInsert over objects. I reach about 75k inserts per second on lower end hardware. Yes, that is 75000.
    – TomTom
    Jun 10 '13 at 15:45
  • @TomTom - would you mind giving me an example of your object that you made the SqlBulkInsert?
    – Jenninha
    Jun 10 '13 at 15:48
  • Simple class with properties. The trick is the SqlBulkCopy implementation that uses a special provider. All that is on the internet though and NOT a question to ask here - programming is StackOverflow. Google has details. "SqlBulkCopy with objects" is something you could use.
    – TomTom
    Jun 10 '13 at 15:49
4

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();
Improve this answer
8
  • Hi - no, the ids I have to insert aren't incremented. They are specific ids and can be any ramdom value. The ones I posted there were pure coincidence, because I used a set of examples as test. But they won't be incremented :-(
    – Jenninha
    Jun 10 '13 at 15:57
  • @Jenninha then please explain where they're coming from and how your code is currently holding 30,000 values - are they in a DataTable or some other collection? A comma-separated list? A text file?
    – Aaron Bertrand
    Jun 10 '13 at 15:59
  • They are ids of a set of search results from a user search that is done outside my application. To be honest I haven't defined how I will receive them yet, so now my test is only by having an array with 30000 ids that I will have to insert in a temporary table in SQL Server.
    – Jenninha
    Jun 10 '13 at 16:03
  • Instead of an array put them in a DataTable. If you don't know how to do that then it will be a question more appropriate for StackOverflow I think. Once your values are in a DataTable, this answer should help.
    – Aaron Bertrand
    Jun 10 '13 at 16:04
  • I did it, I put it in a DataTable, but it was too slow I believe because of my loop to create the DataRows. I will post my SqlBulkCopy insert in a question on StackOverflow and then everyone can see where I might have done something wrong. Thanks!
    – Jenninha
    Jun 10 '13 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.