Re: [sqlite] Insert multiple entries in a single INSERT statement

[CityDev]

Kees Nuyt wrote:
> 
> 
> Insert one row at a time.
> 
> 
> 
 Presumably you can do this kind of thing:

INSERT INTO Table2 ( [FieldX] )
SELECT FieldY
FROM Table1;
-- 
View this message in context: 
http://www.nabble.com/Insert-multiple-entries-in-a-single-INSERT-statement-tp24705205p24737715.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Simon Slavin]

On 28 Jul 2009, at 7:22pm, Shaun Seckman (Firaxis) wrote:

>Looking at the SQL syntax chart it doesn't seem like
> this is possible.  In other SQL servers I'm able to use the statement
> "insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
> '3');".  Is this possible in SQLite or must I instead insert one at a
> time.


One INSERT command = one new record.

Depending on what part of the API you're using, you may be able to  
string multiple INSERT commands together, separated by a semi-colon.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Hoover, Jeffrey]

(apologies, I had a typo in my code sample, here is a correction)
if your are using Perl you can "bulk insert" the data...
.
.
.
use DBI;
our $errorMessage;
.
.
.
my @mydata = ();
{
my @myrow = (1,1);
push @mydata, \...@myrow;
}
{
my @myrow = (2,2);
push @mydata, \...@myrow;
}
{
my @myrow = (3,3);
push @mydata, \...@myrow;
}
my $numinserts = ($dbh, "insert into
foo(col1,col2)
values (?,?)", \...@mydata );
if ( !defined $numinserts ) { die $errorMessage }
.
.
.
sub bulkInsertData {
my ($dbh,$insertSQL,$rowdata) = @_;
$errorMessage = undef;

# prepare insert statement
my $stmt = $dbh->prepare($insertSQL);
if ( !defined $stmt) {
$errorMessage = "bulkInsertData could not prepare SQL
$insertSQL: $DBI::errstr";
return undef;
}

# if no data return without doing anything  
my @data = @$rowdata;
my $nrows = scalar @data;
if ( $nrows == 0 ) { return 0; }

# insert data into table
my @rowStatus = ();
my $numInserted = $stmt->execute_array( { ArrayTupleStatus =>
\...@rowstatus, ArrayTupleFetch => sub{ shift @data } } );
if ( ! defined $numInserted ) {
$errorMessage = "bulkInsertData insert failed: " .
(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . (\...@rowstatus);
return undef;
} else {
$errorMessage = undef;
}

return $numInserted;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Tuesday, July 28, 2009 2:22 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert multiple entries in a single INSERT statement

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Rich Shepard]

On Tue, 28 Jul 2009, Shaun Seckman (Firaxis) wrote:

>   Looking at the SQL syntax chart it doesn't seem like this is possible. 
> In other SQL servers I'm able to use the statement "insert into
> foo('col1', col2') values ('1', '1'), ('2', '2'), ('3', '3');".  Is this
> possible in SQLite or must I instead insert one at a time.

Shaun,

   I've only seen single values per row in the references and that's what
I've done both programmatically and in the shell. Programmatically, use a
loop for new values. In the shell, I write a .sql file that has the INSERT
INTO statements in a transaction.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Hoover, Jeffrey]

if your are using Perl you can "bulk insert" the data...
.
.
.
use DBI;
our $errorMessage;
.
.
.
my @mydata = ();
{
my @myrow = (1,1);
push @mydata, \...@myrow;
}
{
my @myrow = (2,2);
push @mydata, \...@myrow;
}
{
my @myrow = (3,3);
push @mydata, \...@myrow;
}
my $numinserts = ($dbh, "foo('col1', col2')
values (?,?)", \...@mydata );
if ( !defined $numinserts ) { die $errorMessage }
.
.
.
sub bulkInsertData {
my ($dbh,$insertSQL,$rowdata) = @_;
$errorMessage = undef;

# prepare insert statement
my $stmt = $dbh->prepare($insertSQL);
if ( !defined $stmt) {
$errorMessage = "bulkInsertData could not prepare SQL
$insertSQL: $DBI::errstr";
return undef;
}

# if no data return without doing anything  
my @data = @$rowdata;
my $nrows = scalar @data;
if ( $nrows == 0 ) { return 0; }

# insert data into table
my @rowStatus = ();
my $numInserted = $stmt->execute_array( { ArrayTupleStatus =>
\...@rowstatus, ArrayTupleFetch => sub{ shift @data } } );
if ( ! defined $numInserted ) {
$errorMessage = "bulkInsertData insert failed: " .
(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . (\...@rowstatus);
return undef;
} else {
$errorMessage = undef;
}

return $numInserted;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Tuesday, July 28, 2009 2:22 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert multiple entries in a single INSERT statement

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert multiple entries in a single INSERT statement

[Kees Nuyt]

On Tue, 28 Jul 2009 14:22:28 -0400, "Shaun Seckman
(Firaxis)"  wrote:

>Hello,
>
>Looking at the SQL syntax chart it doesn't seem like
>this is possible.  In other SQL servers I'm able to use the statement
>"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
>'3');".  Is this possible in SQLite or must I instead insert one at a
>time.

Insert one row at a time.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert multiple entries in a single INSERT statement

[Shaun Seckman (Firaxis)]

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users