É possível inserir várias linhas por vez em um database SQLite?

No MySQL você pode inserir várias linhas como esta:

INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); 

No entanto, estou recebendo um erro quando tento fazer algo assim. É possível inserir várias linhas por vez em um database SQLite? Qual é a syntax para fazer isso?

atualizar

Como BrianCampbell aponta aqui , o SQLite 3.7.11 e acima agora suporta a syntax mais simples do post original . No entanto, a abordagem mostrada ainda é apropriada se você quiser compatibilidade máxima nos bancos de dados legados.

resposta original

Se eu tivesse privilégios, eu responderia a resposta de Andy : Você pode inserir várias linhas no SQLite, você só precisa de uma syntax diferente . Para deixar isso bem claro, o exemplo do OPs MySQL:

 INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); 

Isso pode ser reformulado no SQLite como:

  INSERT INTO 'tablename' SELECT 'data1' AS 'column1', 'data2' AS 'column2' UNION ALL SELECT 'data1', 'data2' UNION ALL SELECT 'data1', 'data2' UNION ALL SELECT 'data1', 'data2' 

uma nota sobre desempenho

Eu originalmente usei essa técnica para carregar eficientemente grandes conjuntos de dados do Ruby on Rails. No entanto , como Jaime Cook ressalta , não está claro se trata de qualquer INSERTs mais rápida de INSERTs individuais em uma única transação:

 BEGIN TRANSACTION; INSERT INTO 'tablename' table VALUES ('data1', 'data2'); INSERT INTO 'tablename' table VALUES ('data3', 'data4'); ... COMMIT; 

Se a eficiência é o seu objective, você deve tentar primeiro.

uma nota sobre UNION vs UNION ALL

Como várias pessoas comentaram, se você usar UNION ALL (como mostrado acima), todas as linhas serão inseridas, portanto, nesse caso, você obteria quatro linhas de data1, data2 . Se você omitir o ALL , as linhas duplicadas serão eliminadas (e a operação provavelmente será um pouco mais lenta). Estamos usando o UNION ALL, pois ele corresponde mais de perto à semântica do post original.

no fechamento

PS: Por favor, a resposta de +1, não é minha! Ele apresentou a solução primeiro.

Sim, é possível, mas não com os valores usuais de inserção separados por vírgulas.

Tente isso …

 insert into myTable (col1,col2) select aValue as col1,anotherValue as col2 union select moreValue,evenMoreValue union... 

Sim, é um pouco feio, mas fácil o suficiente para automatizar a geração da declaração a partir de um conjunto de valores. Além disso, parece que você só precisa declarar os nomes das colunas na primeira seleção.

Sim, desde o SQLite 3.7.11 isso é suportado no SQLite. Da documentação do SQLite :

Sintaxe da instrução SQLite INSERT

(quando esta resposta foi originalmente escrita, isso não era suportado)

Para compatibilidade com versões mais antigas do SQLite, você pode usar o truque sugerido por andy e fearless_fool usando UNION , mas para o 3.7.11 e posterior a syntax mais simples descrita aqui deve ser preferida.

Escrevi algum código ruby ​​para gerar uma única inserção de várias linhas de 500 elementos a partir de uma série de instruções de inserção que era consideravelmente mais rápida do que executar as inserções individuais. Então, tentei simplesmente agrupar as várias inserções em uma única transação e descobri que poderia obter o mesmo tipo de velocidade com um código consideravelmente menor.

 BEGIN TRANSACTION; INSERT INTO table VALUES (1,1,1,1); INSERT INTO table VALUES (2,2,2,2); ... COMMIT; 

De acordo com esta página , não é suportado:

  • 2007-12-03: INSERT composto de múltiplas linhas aka INSERT não suportado.
  INSERT INTO table (col1, col2) VALUES ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ... 

Na verdade, de acordo com o padrão SQL92, uma expressão VALUES deve ser capaz de se sustentar sozinha. Por exemplo, o seguinte deve retornar uma tabela de uma coluna com três linhas: VALUES 'john', 'mary', 'paul';

A partir da versão 3.7.11, o SQLite suporta inserção de múltiplas linhas . Comentários de Richard Hipp:

“A nova inserção de múltiplos valores é meramente um suplemento sintático (sic) para a inserção do composto. Não há vantagem de desempenho de uma forma ou de outra.”

A partir da versão 2012-03-20 (3.7.11), o sqlite suporta a seguinte syntax de INSERT:

 INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data3', 'data4'), ('data5', 'data6'), ('data7', 'data8'); 

Leia a documentação: http://www.sqlite.org/lang_insert.html

PS: Por favor, +1 à resposta / resposta de Brian Campbell. não é meu! Ele apresentou a solução primeiro.

Como os outros pôsteres disseram, o SQLite não suporta esta syntax. Não sei se os INSERTs compostos fazem parte do padrão SQL, mas, na minha experiência, eles não são implementados em muitos produtos.

Como um aparte, você deve estar ciente de que o desempenho do INSERT no SQLite é melhorado consideravelmente se você envolver vários INSERTs em uma transação explícita.

Sim, o sql pode fazer isso, mas com uma syntax diferente. A propósito, a documentação do sqlite é muito boa. Ele também informará que a única maneira de inserir várias linhas é usar uma instrução select como a origem dos dados a serem inseridos.

O Sqlite3 não pode fazer isso diretamente no SQL, exceto através de um SELECT, e enquanto o SELECT pode retornar uma “linha” de expressões, não sei como fazer com que ele retorne uma coluna falsa.

No entanto, o CLI pode fazer isso:

 .import FILE TABLE Import data from FILE into TABLE .separator STRING Change separator used by output mode and .import $ sqlite3 /tmp/test.db SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table abc (a); sqlite> .import /dev/tty abc 1 2 3 99 ^D sqlite> select * from abc; 1 2 3 99 sqlite> 

Se você colocar um loop em torno de um INSERT, em vez de usar o comando CLI .import , certifique-se de seguir os conselhos da FAQ do sqlite para a velocidade do INSERT:

Por padrão, cada instrução INSERT é sua própria transação. Mas se você colocar várias instruções INSERT com BEGIN … COMMIT, todas as inserções serão agrupadas em uma única transação. O tempo necessário para confirmar a transação é amortizado em relação a todas as instruções de inserção incluídas e, portanto, o tempo por instrução de inserção é bastante reduzido.

Outra opção é executar o PRAGMA synchronous = OFF. Esse comando fará com que o SQLite não espere que os dados cheguem à superfície do disco, o que tornará as operações de gravação muito mais rápidas. Mas se você perder energia no meio de uma transação, seu arquivo de database pode ficar corrompido.

Alex está correto: a declaração “select … union” perderá a ordem que é muito importante para alguns usuários. Mesmo quando você insere em uma ordem específica, o sqlite muda as coisas, então prefira usar transactions se a ordem de inserção for importante.

 create table t_example (qid int not null, primary key (qid)); begin transaction; insert into "t_example" (qid) values (8); insert into "t_example" (qid) values (4); insert into "t_example" (qid) values (9); end transaction; select rowid,* from t_example; 1|8 2|4 3|9 

O fearless_fool tem uma ótima resposta para versões mais antigas. Eu só queria acrescentar que você precisa ter certeza de ter todas as colunas listadas. Então, se você tem 3 colunas, você precisa se certificar de que atua em 3 colunas.

Exemplo: tenho 3 colunas, mas só quero inserir duas colunas de dados. Suponha que eu não me importe com a primeira coluna porque é um id inteiro padrão. Eu poderia fazer o seguinte …

 INSERT INTO 'tablename' SELECT NULL AS 'column1', 'data1' AS 'column2', 'data2' AS 'column3' UNION SELECT NULL, 'data3', 'data4' UNION SELECT NULL, 'data5', 'data6' UNION SELECT NULL, 'data7', 'data8' 

Nota: Lembre-se que a declaração “select … union” perderá a ordem. (De AG1)

Você não pode, mas eu não acho que você sente falta de nada.

Como você chama o sqlite sempre em processo, quase não importa se você executa 1 instrução de inserção ou 100 instruções de inserção. O commit, no entanto, leva muito tempo, então coloque essas 100 inserções dentro de uma transação.

O Sqlite é muito mais rápido quando você usa consultas parametrizadas (muito menos análise necessária) para que eu não concateneasse instruções grandes como esta:

 insert into mytable (col1, col2) select 'a','b' union select 'c','d' union ... 

Eles precisam ser analisados ​​de novo e de novo porque cada declaração concatenada é diferente.

no mysql lite você não pode inserir vários valores, mas você pode economizar tempo abrindo a conexão apenas uma vez e depois fazendo todas as inserções e fechando a conexão. Isto economiza muito tempo

 INSERT INTO TABLE_NAME (DATA1, DATA2) VALUES (VAL1, VAL2), (VAL1, VAL2), (VAL1, VAL2), (VAL1, VAL2), (VAL1, VAL2), (VAL1, VAL2), (VAL1, VAL2), (VAL1, VAL2); 

O problema com o uso da transação é que você bloqueia a tabela também para leitura. Portanto, se você tiver muitos dados para inserir e precisar acessar seus dados, por exemplo, uma prévia ou algo assim, isso não funciona bem.

O problema com a outra solução é que você perde a ordem da inserção

 insert into mytable (col) select 'c' union select 'd' union select 'a' union select 'b'; 

No sqlite os dados serão armazenados a, b, c, d …

A partir da versão 3.7.11, o SQLite suporta inserção de múltiplas linhas. Comentários de Richard Hipp:

Estou usando 3.6.13

Eu comando assim:

 insert into xtable(f1,f2,f3) select v1 as f1, v2 as f2, v3 as f3 union select nextV1+, nextV2+, nextV3+ 

Com 50 registros inseridos por vez, leva apenas um segundo ou menos.

É verdade que usar o sqlite para inserir várias linhas por vez é muito possível. Por @ Andy escreveu.

obrigado Andy +1

 INSERT INTO tabela(coluna1,coluna2) SELECT 'texto','outro' UNION ALL SELECT 'mais texto','novo texto'; 

Se você usar o plugin firefox do Sqlite manager , ele suportará inserções em massa a partir de INSERT SQL INSERT .

De fato, ele não suporta isso, mas o Sqlite Browser funciona (funciona no Windows, OS X, Linux)

Eu tenho uma consulta como abaixo, mas com o driver ODBC SQLite tem um erro com “,” diz. Eu corro vbscript em HTA (Html ​​Application).

 INSERT INTO evrak_ilac_iliskileri (evrak_id, ilac_id, baglayan_kullanici_id, tarih) VALUES (4150,762,1,datetime()),(4150,9770,1,datetime()),(4150,6609,1,datetime()),(4150,3628,1,datetime()),(4150,9422,1,datetime()) 

No sqlite 3.7.2:

 INSERT INTO table_name (column1, column2) SELECT 'value1', 'value1' UNION SELECT 'value2', 'value2' UNION SELECT 'value3', 'value3' 

e assim por diante

Eu sou capaz de tornar a consulta dinâmica. Esta é minha mesa:

CREATE TABLE "tblPlanner" ("probid" text,"userid" TEXT,"selectedtime" DATETIME,"plannerid" TEXT,"isLocal" BOOL,"applicationid" TEXT, "comment" TEXT, "subject" TEXT)

e eu estou recebendo todos os dados através de um JSON , então depois de obter tudo dentro de um NSArray eu segui isso:

  NSMutableString *query = [[NSMutableString alloc]init]; for (int i = 0; i < arr.count; i++) { NSString *sqlQuery = nil; sqlQuery = [NSString stringWithFormat:@" ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@'),", [[arr objectAtIndex:i] objectForKey:@"plannerid"], [[arr objectAtIndex:i] objectForKey:@"probid"], [[arr objectAtIndex:i] objectForKey:@"userid"], [[arr objectAtIndex:i] objectForKey:@"selectedtime"], [[arr objectAtIndex:i] objectForKey:@"isLocal"], [[arr objectAtIndex:i] objectForKey:@"subject"], [[arr objectAtIndex:i] objectForKey:@"comment"], [[NSUserDefaults standardUserDefaults] objectForKey:@"applicationid"] ]; [query appendString:sqlQuery]; } // REMOVING LAST COMMA NOW [query deleteCharactersInRange:NSMakeRange([query length]-1, 1)]; query = [NSString stringWithFormat:@"insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values%@",query]; 

E finalmente a consulta de saída é esta:

 insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values  ('pl1176428260', '', 'US32552', '2013-06-08 12:00:44 +0000', '0', 'subj', 'Hiss', 'ap19788'),  ('pl2050411638', '', 'US32552', '2013-05-20 10:45:55 +0000', '0', 'TERI', 'Yahoooooooooo', 'ap19788'),  ('pl1828600651', '', 'US32552', '2013-05-21 11:33:33 +0000', '0', 'test', 'Yest', 'ap19788'),  ('pl549085534', '', 'US32552', '2013-05-19 11:45:04 +0000', '0', 'subj', 'Comment', 'ap19788'),  ('pl665538927', '', 'US32552', '2013-05-29 11:45:41 +0000', '0', 'subj', '1234567890', 'ap19788'),  ('pl1969438050', '', 'US32552', '2013-06-01 12:00:18 +0000', '0', 'subj', 'Cmt', 'ap19788'),  ('pl672204050', '', 'US55240280', '2013-05-23 12:15:58 +0000', '0', 'aassdd', 'Cmt', 'ap19788'),  ('pl1019026150', '', 'US32552', '2013-06-08 12:15:54 +0000', '0', 'exists', 'Cmt', 'ap19788'),  ('pl790670523', '', 'US55240280', '2013-05-26 12:30:21 +0000', '0', 'qwerty', 'Cmt', 'ap19788') 

que está funcionando bem através de código também e eu sou capaz de salvar tudo no SQLite com sucesso.

Antes disso, tornei as coisas de consulta do UNION dinâmicas, mas isso começou a dar algum erro de syntax. De qualquer forma, isso está funcionando bem para mim.

Estou surpreso que ninguém tenha mencionado declarações preparadas . A menos que você esteja usando SQL por conta própria e não em qualquer outra linguagem, então eu pensaria que as instruções preparadas envolvidas em uma transação seriam a maneira mais eficiente de inserir várias linhas.

você pode usar o InsertHelper, é fácil e rápido

documentação: http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html

tutorial: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/

Editar: InsertHelper está obsoleto a partir do nível 17 da API

Se você está usando o bash shell, você pode usar isto:

 time bash -c $' FILE=/dev/shm/test.db sqlite3 $FILE "create table if not exists tab(id int);" sqlite3 $FILE "insert into tab values (1),(2)" for i in 1 2 3 4; do sqlite3 $FILE "INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5"; done; sqlite3 $FILE "select count(*) from tab;"' 

Ou se você estiver no sqlite CLI, então você precisa fazer isso:

 create table if not exists tab(id int);" insert into tab values (1),(2); INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5; INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5; INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5; INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5; select count(*) from tab; 

Como funciona? Faz uso dessa tab se tabela:

 id int ------ 1 2 

em seguida, select a.id, b.id from tab a, tab b retorna

 a.id int | b.id int ------------------ 1 | 1 2 | 1 1 | 2 2 | 2 

e assim por diante. Após a primeira execução, inserimos 2 linhas, depois 2 ^ 3 = 8. (três porque nós temos tab a, tab b, tab c )

Após a segunda execução, inserimos linhas adicionais (2+8)^3=1000

Aftern thrid inserimos sobre max(1000^3, 5e5)=500000 linhas e assim por diante …

Esse é o método mais rápido conhecido para o preenchimento do database SQLite.