0

I want to add multiple items/rows in SQLite using single insert query,

 insert into suppliers (supoliers_no,supply_name,city,phone_no)
      values (1,'Ali','amman',111111), (2,'tariq','amman',777777), (3,'mohmmed','taiz',null);

Is it possible using Sqlite?

dbw
  • 5,842
  • 2
  • 21
  • 56
Mahmoud Albluwi
  • 41
  • 1
  • 5
  • 2
    insert into suppliers (supoliers_no,supply_name,city,phone_no) values (1,"Ali","amman",111111); – Raghunandan Oct 26 '13 at 07:11
  • please specify your database. this syntax suits mysql, but for sqllite its different. http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database – Admit Oct 26 '13 at 07:15
  • are you using SQL2008? I think above syntex valid after 2008 – Snehal Oct 26 '13 at 07:16
  • 1
    why was it tagged under ios java. is it sqlite3 you are using? if so it should be tagged under it – Raghunandan Oct 26 '13 at 07:17

5 Answers5

3

Multiple insert rows in one line code is

INSERT INTO TableName ( Column1, Column2,Column3 ) VALUES ( Value1, Value2 ,Value3), ( Value1, Value2,Value3 );

In your case Code is correct but

(3,'mohmmed','taiz',null); try with give some value. and you can also take reference from here

Inserting multiple rows in a single SQL query?

Community
  • 1
  • 1
Shailendr singh
  • 676
  • 5
  • 19
2

It should be:- 

insert into suppliers (supoliers_no,supply_name,city,phone_no) values (1,'Ali','amman',111111);

insert into suppliers (supoliers_no,supply_name,city,phone_no) values  (2,'tariq','amman',777777);

insert into suppliers (supoliers_no,supply_name,city,phone_no) values  (3,'mohmmed','taiz',null);

Also insert into suppliers (supoliers_no,supply_name,city,phone_no) values (3,'mohmmed','taiz',null); as null is not a recognized keyword in sql. So you might have to try it out like insert into suppliers (supoliers_no,supply_name,city,phone_no) values (3,'mohmmed','taiz','');

d3m0li5h3r
  • 1,960
  • 16
  • 31
2

This is possible but the way to do this is different in SQLITE,

Try this...

insert into myTable (mycol1,mycol2) 
select aValue as mycol1,anotherValue as mycol2 
union select moreValue,evenMoreValue 
union...

in your case it will be as,

    INSERT INTO suppliers 
        SELECT 1 AS supoliers_no, 'Ali' AS supply_name, 'amman' AS city, 111111 AS phone_no
  UNION SELECT 2 , 'tariq' , 'amman' , 77777 
  UNION SELECT 3 , 'mohmmed' , 'taiz', null

Remember null in small letter do work in SQL Lite as I have created this table and phone_no column in INT in my case

dbw
  • 5,842
  • 2
  • 21
  • 56
  • 1
    @MahmoudAlbluwi this answer for you single query – dbw Oct 26 '13 at 08:02
  • On the one hand, that's a single top-level query, on the other hand it's also four nested queries. Whether tis better or worse depends on the optimizer and your internal style rules. Nice alternate solution though. There should be a checkbox for "also interesting". – tekHedd Oct 30 '20 at 21:15
0

Inserting multiple records with a single INSERT statement is supported only in SQLite 3.7.11 and later. On earlier versions, you have to use INSERT ... SELECT ... with UNION ALL, or use multiple statements.

CL.
  • 158,085
  • 15
  • 181
  • 214
-1

We are assume that you have a well design for your table, now you can insert more than one record into a table : 

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008, this work else you have a problem in your design such as you can't insert a null data into your table.

Hamid Talebi
  • 1,240
  • 2
  • 22
  • 39