3

I have a list with values in it. I want to insert these values in a SQL table using a single INSERT statement.

Example: Say, there is a list with names (the size of the list is not constant). There is a STUDENTS table with NAME column. I want to insert in the STUDENTS table the names from the list using a single INSERT statement.

Right now I loop through the list and insert the value in the table. That means, the number of insert statements is equal to the length of the list.

List<String> Name;
foreach (String s in Name)
{
  INSERT INTO STUDENTS (NAME) VALUES (s)
}

Is there a way I can accomplish this in a single SQL INSERT statement? Any help is much appreciated.

Devi Sadhu
  • 33
  • 1
  • 1
  • 3
  • if you need one string to be executed you can merge sql statements with ; like `INSERT INTO STUDENTS (NAME) VALUES (s1);INSERT INTO STUDENTS (NAME) VALUES (s2);INSERT INTO STUDENTS (NAME) VALUES (s3);INSERT INTO STUDENTS (NAME) VALUES (s4)` – user2102266 Aug 20 '14 at 21:02
  • http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query – Slippery Pete Aug 20 '14 at 21:02
  • What language are you using here? – Jeff Aug 20 '14 at 21:02
  • What RDBMS are you using? Please update the tags with this. – Barmar Aug 20 '14 at 21:12

5 Answers5

5

In MySql you can do it like this

INSERT INTO tbl (f1,f2) VALUES(1,2),(3,4),(5,6)...(55,77);

For mssql dialect all the same

Dmitry Bezik
  • 349
  • 1
  • 5
  • @DeanOC How can you forget the syntax? It is a "normal" INSERT statement with several VALUES blocks instead one. There is no much difference :D. – Tom Aug 20 '14 at 21:12
  • You can use this if you know the values that you are inserting (kind of hard-coding the values). In my case, the values live in a list, and the size of the list is not constant. So I cannot say VALUES ("A", "B). – Devi Sadhu Aug 20 '14 at 21:12
  • but you can build string like this (1,2),.... in foreach cycle and after concat 2 strings in one statement, this was a question?) – Dmitry Bezik Aug 20 '14 at 21:16
  • @DeviSadhu You can use a StringBuilder for your query and append a VALUES block inside your for loop like this `sb.append("(" + s + ")");`. You just need to find a suitable way to add a `,` after each block. You may add it to the append and cut of the last one or you make an if statement for it. – Tom Aug 20 '14 at 21:17
  • @Tom I forget many things. I cannot even recall how many things I forget. – DeanOC Aug 20 '14 at 21:34
  • That sounds great. I can use a StringBuilder and create the string for "VALUES(), (), ()" and then use that string in the INSERT statement. I'll try it out and let you know if it worked. As of now, I'll go ahead and accept this answer. Many thanks, guys :-) – Devi Sadhu Aug 20 '14 at 22:12
0

Union can be used to achieve this, though technically each union is it's own statement.

short demo:

create table #i(hello int)
insert into #i select 1 union all select 2
select * from #i
drop table #i

There are other / better ways of populating tables from lists...those depend on what database you are on.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
0

To do this with a single INSERT is with a SELECT.. you'd have to convert your list into this:

INSERT STUDENTS(NAME)
SELECT 'Tom'
UNION ALL
SELECT 'Bill'
UNION ALL
SELECT 'Sarah'
T McKeown
  • 12,971
  • 1
  • 25
  • 32
0

If your list was in a temporary table, you could use the following syntax:

insert into tblname
    (columnlist)
select name from temptable;
Nick Pierpoint
  • 17,641
  • 9
  • 46
  • 74
JM_developer
  • 58
  • 5
0

with string.join

private void insertInto(string tablename, List<Values> list)
{
                                       
context.Database.ExecuteSqlRaw($@"INSERT INTO {tablename}(number,comment) VALUES                                                   
                               {string.Join(',',list.ToArray().Select(r =>$" 
                               ({r.value},'{r.comment}')").ToList())}");

returns list data and result : VALUES(1,'hello'),(5,'hello2').....

B_Cbk
  • 78
  • 11