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)

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. msdn.microsoft.com/en-us/library/bb510489.aspx — paparazzo
– paparazzo, Commented Feb 17, 2014 at 17:49

eoldre · Accepted Answer · 2014-02-17 16:57:59Z

3

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);

edited Feb 17, 2014 at 16:57

answered Feb 17, 2014 at 16:51

eoldre

1,08718 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:20:57Z

1

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.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Feb 17, 2014 at 16:55

Clement Dungler

7476 silver badges10 bronze badges

Comments

Deepak · Accepted Answer · 2014-02-17 17:04:28Z

1

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)

answered Feb 17, 2014 at 17:04

Deepak

8,0073 gold badges26 silver badges26 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:31:56Z

1

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

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Feb 18, 2014 at 1:23

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

insert multiple rows with one query using a C# array

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related