Running multiple insert requests into firebird database using isql

Question

I have a requirement inserting enormous data in table of firebird database around 40K entries . I got my scripts, but when I executed it using the Robin flame, the user interface simply hung forever, inserting such huge data at a time.

I know that it would be nice if I fulfilled my insert requests in blocks of 255 requests, but I want to know if there is a bulk insert tool available for Firebird for these records when reading from scripts.sql file.

After some googling, I came across an isql tool, but could not execute scripts against it. Can someone lead me to any other tool or proper documentation for entering such huge data at a time?

I have firebird version 2.5 installed on my system.

Answer 1

40K doesn't seem big to me. In our cases, we work with millions of records without significant problems. I think the cause of starvation is Flamerobin, trying to parse entire scripts or something like that. Use isql instead. First prepare a text file with the commands:

 CONNECT "your_server:your_database_name" USER "sysdba" PASSWORD "masterkey"; INSERT ... ; INSERT ... ; .... INSERT ... ; EXIT; 

Then run the utility from the dos prompt:

 isql < your_script.sql 

Also consider disabling indexes and resetting foreign keys for a table for a bulk insert operation and subsequent restore.

Answer 2

You can use EXECUTE BLOCK to add more attachments to a single statement. Something like that:

 set term !! ; EXECUTE BLOCK AS BEGIN insert into ... values ...; insert into ... values ...; insert into ... values ...; insert into ... values ...; ....etc. insert into ... values ...; END!! 

You can group them 100 at a time or the like. This should speed up the work, as well as improve FlameRobin parsing.

isql is still faster, but it gives you better error control. It is very difficult to debug material if some inserts in the middle do not work with isql.

Answer 3

You can use EXTERNAL .

 command.CommandText = @"CREATE TABLE TEMP1 EXTERNAL FILE 'c:\ExternalTables\Ext.ext'(ID INTEGER,CRLF CHAR(2));"; int j=command.ExecuteNonQuery();