Re: [sqlite] INSERT INTO SQLite from a FixedLength text file

[Oliver Peters]

  writes:

> 
> > If we import the same text file into a MS Access database using INSERT INTO
MDE SELECT * FROM 
> > [Text;Database=C:\\folder].[filename.txt], it takes about a minute and half
but because SQLite does
> not support that, I have to 
> > use the insert into statement for every record inside a transaction (every
10,000 records), it's taking 6
> mins 30 secs to import 
> > the whole file. 
> 
> > >Make sure you prepare the statement once, then just bind parameters for
every row. 
> 
> In general, below is what I am doing... 
> cmd->CommandText = "INSERT INTO tablename VALUES (@field1, @field2, ...)"; 
> adpt->InsertCommand = cmd; 
> setup params 
> 
> while read line from text file 
> insert record to DataTable 
> every 1 records 
> { 
> SQLiteTransaction^ t = cn->BeginTransaction(); 
> cmd->Transaction = t; 
> adpt->Update(DataTable); 
> t->Commit(); 
> } 

try to simplify by only using the CLI (sqlite3) and the file with your INSERT
statements as 1 transaction. If you still need this much time you might use
indexes or Foreign Key constraints that slow the whole thing down.

oliver



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

[sqlite] INSERT INTO SQLite from a FixedLength text file

[asheu]

> If we import the same text file into a MS Access database using INSERT INTO 
> MDE SELECT * FROM 
> [Text;Database=C:\\folder].[filename.txt], it takes about a minute and half 
> but because SQLite does not support that, I have to 
> use the insert into statement for every record inside a transaction (every 
> 10,000 records), it's taking 6 mins 30 secs to import 
> the whole file. 

> >Make sure you prepare the statement once, then just bind parameters for 
> >every row. 

In general, below is what I am doing... 
cmd->CommandText = "INSERT INTO tablename VALUES (@field1, @field2, ...)"; 
adpt->InsertCommand = cmd; 
setup params 

while read line from text file 
insert record to DataTable 
every 1 records 
{ 
SQLiteTransaction^ t = cn->BeginTransaction(); 
cmd->Transaction = t; 
adpt->Update(DataTable); 
t->Commit(); 
} 


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

Re: [sqlite] INSERT INTO SQLite from a FixedLength text file

[Oliver Peters]

  writes:

> 
> 
> Thank you for the responses. 
> If we import the same text file into a MS Access database using INSERT INTO
MDE SELECT * FROM
> [Text;Database=C:\\folder].[filename.txt], it takes about a minute and half
but because SQLite does
> not support that, I have to use the insert into statement for every record
inside a transaction (every
> 10,000 records), it's taking 6 mins 30 secs to import the whole file. 
> 
> I was hoping there was a way to speed up that process up a little more. 
> 

Simply structure the whole thing like this:

BEGIN TRANSACTION;
INSERT INTO tablename(field01,field02,...) VALUES(val01,val02,...);
INSERT INTO tablename(field01,field02,...) VALUES(val01,val02,...);
INSERT INTO tablename(field01,field02,...) VALUES(val01,val02,...);
COMMIT;

btw: put strings between ''

the language is documented here http://www.sqlite.org/lang.html

oliver

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

Re: [sqlite] INSERT INTO SQLite from a FixedLength text file

[Igor Tandetnik]

as...@comcast.net wrote:
> If we import the same text file into a MS Access database using INSERT INTO 
> MDE SELECT * FROM
> [Text;Database=C:\\folder].[filename.txt], it takes about a minute and half 
> but because SQLite does not support that, I have to
> use the insert into statement for every record inside a transaction (every 
> 10,000 records), it's taking 6 mins 30 secs to import
> the whole file.

Make sure you prepare the statement once, then just bind parameters for every 
row.
-- 
Igor Tandetnik

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

[sqlite] INSERT INTO SQLite from a FixedLength text file

[asheu]

Thank you for the responses. 
If we import the same text file into a MS Access database using INSERT INTO MDE 
SELECT * FROM [Text;Database=C:\\folder].[filename.txt], it takes about a 
minute and half but because SQLite does not support that, I have to use the 
insert into statement for every record inside a transaction (every 10,000 
records), it's taking 6 mins 30 secs to import the whole file. 

I was hoping there was a way to speed up that process up a little more. 


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

Re: [sqlite] INSERT INTO SQLite from a FixedLength text file

[Igor Tandetnik]

as...@comcast.net wrote:
> I know you can insert into MS Access from a FixedLength text file using 
> schema.ini with this:
> INSERT INTO MDE SELECT * FROM [Text;Database=C:\\folder].[filename.txt]
> 
> Is this supported in SQLite.

No.

> I know you can use import command to import a csv to sqlite but I am looking 
> for sql statement
> that does the work.  

There is no such statement. .import command parses the file and issues a 
regular INSERT statement for each line.

> I know you can also read the records in and then using insert statement on 
> each records to insert into sqlite. I have done 't
> that but it's a little slower

Slower than what?
-- 
Igor Tandetnik

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

Re: [sqlite] INSERT INTO SQLite from a FixedLength text file

[Oliver Peters]

  writes:

> 
> I know you can insert into MS Access from a FixedLength text file using
schema.ini with this: 
> INSERT INTO MDE SELECT * FROM [Text;Database=C:\\folder].[filename.txt] 
> 
> Is this supported in SQLite. I have tried many syntax and getting syntax
error. I am not sure if I have the
> syntax incorrect or it's just not supported in SQLite. I know you can use
import command to import a csv to
> sqlite but I am looking for sql statement that does the work. 
> 
> I know you can also read the records in and then using insert statement on
each records to insert into sqlite.
> I have done 't that but it's a little slower than we would like and is looking
for something to speed up the
> process. 

No, it is not supported in sqlite - but it's simple to preprocess the whole file
with the language of your choice (i.e. gawk is optimised for such problems)

greetings
oliver

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

[sqlite] INSERT INTO SQLite from a FixedLength text file

[asheu]

I know you can insert into MS Access from a FixedLength text file using 
schema.ini with this: 
INSERT INTO MDE SELECT * FROM [Text;Database=C:\\folder].[filename.txt] 

Is this supported in SQLite. I have tried many syntax and getting syntax error. 
I am not sure if I have the syntax incorrect or it's just not supported in 
SQLite. I know you can use import command to import a csv to sqlite but I am 
looking for sql statement that does the work. 

I know you can also read the records in and then using insert statement on each 
records to insert into sqlite. I have done 't that but it's a little slower 
than we would like and is looking for something to speed up the process. 

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