Insert large amount of data efficiently with SQL

Hi I often have to insert a lot of data into a table. For example, I would have data from excel or text file in the form of

1,a
3,bsdf
4,sdkfj
5,something
129,else

then I often construct 6 insert statements in this exampl开发者_如何学Pythone and run the SQL script. I found this was slow when I have to send thousands of small packages to server, it also causes extra overhead to the network.

What's your best way of doing this?

Update: I'm using ORACLE 10g.

Use Oracle external tables.

See also e.g.

• OraFaq about external tables
• What Tom thinks about external tables
• René Nyffenegger's notes about external tables

A simple example that should get you started

You need a file located in a server directory (get familiar with directory objects):

SQL> select directory_path from all_directories where directory_name = 'JTEST';

DIRECTORY_PATH
--------------------------------------------------------------------------------
c:\data\jtest

SQL> !cat ~/.gvfs/jtest\ on\ 192.168.xxx.xxx/exttable-1.csv
1,a
3,bsdf
4,sdkfj
5,something
129,else

Create an external table:

create table so13t (
  id number(4),
  data varchar2(20)
)
organization external (
  type oracle_loader
  default directory jtest /* jtest is an existing directory object */
  access parameters (
    records delimited by newline
    fields terminated by ','
    missing field values are null
  )
  location ('exttable-1.csv') /* the file located in jtest directory */
)
reject limit unlimited;

Now you can use all the powers of SQL to access the data:

SQL> select * from so13t order by data;

        ID DATA
---------- ------------------------------------------------------------
         1 a
         3 bsdf
       129 else
         4 sdkfj
         5 something

Im not sure if this works in Oracle but in SQL Server you can use BULK INSERT sql statement to upload data from a txt or a csv file.

BULK
INSERT [TableName]
FROM 'c:\FileName.txt'
WITH 
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
GO

Just make sure that the table columns correctly matches whats in the txt file. With a more complicated solution you may want to use a format file see the following: http://msdn.microsoft.com/en-us/library/ms178129.aspx

There are alot of ways to speed this up.

1) Do it in a single transaction. This will speed things up by avoiding connection opening / closing.

2) Load directly as a CSV file. If you load data as a CSV file, the "SQL" statements aren't required at all. in MySQL the "LOAD DATA INFILE" operation accomplishes this very intuitively and simply.

3) You can also simply dump the whole file as text into a table called "raw". And then let the database parse the data on its own using triggers. This is a hack, but it will simplify your application code and reduce network usage.