Run multiple insert queries against firebird database using isql

Question

I have requirement of inserting enormous data in table of firebird database around 40K entries. I got my scripts ready but while executing it using flameRobin, the UI just got hang forever while inserting such enormous data in one go.

I know it would be fine if i execute my insert queries in blocks of 255 queries but i want to know if there is any bulk insert tool available for Firebird to do such entries while reading from my scripts.sql file.

After some googling, I came across isql tool but not able to execute the scripts against it. Can someone guide me to any other tool or the proper documentation to enter such enormous data in one go?

I have firebird version 2.5 installed on my system.

About using isql - why are you "not able to execute the scripts against it"? Dou you get an error? If so then what is the error message? isql which comes with FB is the official "command line interface" to FB and in general it works fine. BTW add COMMIT WORK; statement into your script after, say, each 1000 statements, to see does it make difference. — ain
– ain, Commented Oct 20, 2012 at 8:55
I just tested with 3-4 insert queries, it works fine. But with 40K insert queries it seems to get hang. I followed the approach as per this question here - stackoverflow.com/questions/5821562/…. Works good for 2-3 queries but not with such enormous data. :( — Rohit Vats
– Rohit Vats, Commented Oct 20, 2012 at 9:04
Add the COMMIT WORK; statement after every 1000 insert statements or so to see does it make difference. — ain
– ain, Commented Oct 20, 2012 at 9:07
Command Prompt was in hang state for almost 10-12 mins but data was successfully inserted when i checked the database once prompt becomes active. Thanks..!! — Rohit Vats
– Rohit Vats, Commented Oct 20, 2012 at 9:13

Milan Babuškov · Accepted Answer · 2012-10-25 09:29:43Z

8

You can use EXECUTE BLOCK to stuff more inserts into a single statement. Something like this:

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 in 100 at a time or similar. This should make things go much faster and also improve FlameRobin's parsing.

isql is still faster, but this gives you better error control. It's really hard to debug stuff if some inserts in the middle fail with isql.

answered Oct 25, 2012 at 9:29

Milan Babuškov

61.7k49 gold badges131 silver badges182 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrej Kirejeŭ · Accepted Answer · 2012-10-20 09:06:13Z

5

40K doesn't seem a big number for me. In our cases we work with millions of records without significant problems. I think the reason of hunging is Flamerobin trying to parse whole script or something like this. Use isql utility instead. First, prepare a text file with commands:

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

Then run an utility from a dos prompt:

isql < your_script.sql

Also consider to deactivate indices and drop foreign keys for the table for a bulk insert operation and restore them afterwards.

answered Oct 20, 2012 at 9:06

Andrej Kirejeŭ

5,5612 gold badges29 silver badges33 bronze badges

3 Comments

Rohit Vats Over a year ago

I tried this with small data but everytime i run this in prompt i got this message- Use CONNECT or CREATE DATABASE to specify a database. But when i checked my database, entry was there in table. Did you too get this message?

Andrej Kirejeŭ Over a year ago

No. It works fine for me. I can send you a small database and a script.

Rohit Vats Over a year ago

I just tested with my scripts too. After 10-12 mins, message did appear but when i check the database, entries where there. So, message don't matter as long as i am getting the ouput. Thanks a lot.!!

Harshal Doshi Jain · Accepted Answer · 2014-02-08 19:40:39Z

0

You may use EXTERNAL.

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

answered Feb 8, 2014 at 19:40

Harshal Doshi Jain

2,6191 gold badge22 silver badges16 bronze badges

Collectives™ on Stack Overflow

Run multiple insert queries against firebird database using isql

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related