(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 = &bulkInsertData($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: " .
&bulkErrorMessage(\...@rowstatus);
return undef;
} elsif ( ! $numInserted == $nrows ) {
my $numFailed = $nrows - $numInserted;
$errorMessage = "bulkInsertData $numFailed rows failed
to insert: " . &bulkErrorMessage(\...@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
