insert multiple rows with one query using a C# array

Question

I have a list of userIds

List<int> userIds = new List<int>();

I have code to insert them in to db.
ExecuteSQL(sql) is my own function that abstracts out all the db specific logic

string iSql = "";
foreach(int id in userIds){

 iSql = "insert into users (id) values (" + id + ")";
 ExecuteSQL(iSql);

}

I want to insert all of these in one query, without having to call a loop like above, is this possible in SQL?

If so, what would the string iSql look like?

I found Inserting multiple rows in a single SQL query?,

but that may create a large string.

Other articles i found were either other languages or just confusing as i couldn't relate them to my problem

(i can take care of parameterization later, i just need the query)

Answer 1

StringBuilder sb = new StringBuilder();
foreach(int id in userIds){
     sb.AppendLine("insert into users (id) values (" + id.ToString() + ");");
}
ExecuteSQL(sb.ToString());

You really should be using parameterized queries. But the above will do what you need.

or alternatively.

string iSql = string.Format("insert into users (id) values {0};", string.Join(",", userIds.Select(i => string.Format("({0})", i))));
ExecuteSQL(iSql);

Answer 2

I suggest you look at this thread : How should I multiple insert multiple records?

There is a nice way to do what you want.

If you still want to use a string as query you can do something like :

    string iSql = "insert into users (id) values";
foreach(int id in userIds){
 iSql += "(" + id + "),";

}
 ExecuteSQL(iSql);

but honestly, I don't like it :)

Edit : Eoldre answer with the StringBuilder is better than mine.

Answer 3

You can make a stored procedure and send your inserted data(userid) with a xml string. In the stored procedure you can use single insert/select query and inserted all data in table. Please use following code

 create proc insertuser
  @xml  xml
  as

 set @xml=N'<ids>
 <user><id>127531</id></user>  
 <user><id>5445</id> </user> 
<user><id>477</id>  </user>
<user><id>785</id>  </user>
<user><id>456</id> </user>
</ids>'                                    
declare @tbl table(id int)

insert into users(id)
select r.c.value('id[1]','int') 
from @xml.nodes('ids/user') as r(c) 

Answer 4

In case you use this as an answer
I use TVP to load up to 1 million rows and it is fast
It basically works like a reverse DataReader

As you got in some answers you can insert up to I think 1000 using the values (1), (2). If more than 1000 then can use TVP.

TVP

If it is a collection (not a DataTable) then use

SqlDataRecord

enter link description here