This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL Server bulk insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Server bulk insert" Watch "SQL Server bulk insert" New topic
Author

SQL Server bulk insert

Sasikanth Malladi
Ranch Hand

Joined: Nov 04, 2000
Posts: 175
Hi all, I need to insert a few text (csv) files into a remote MS SQL Server.
I tried to use a generic sql client (aqua data studio) but it seems to be generating an insert for each record. Very inefficient and takes hours for even moderate size files (50K records).
I was thinking of using jtds to do bulk insert. I don't have the MS SQL Server native client so I thought of implementing it myself.
Has anyone does this?
Can you share your experience/tips/pointers etc?
TIA,
Sashi
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I tried to use a generic sql client (aqua data studio) but it seems to be generating an insert for each record. Very inefficient and takes hours for even moderate size files (50K records).

How else would you insert stuff in a database? How would you do it with a different client? Are you intending writing your own version of DTS?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

I don't have the MS SQL Server native client so I thought of implementing it myself.
If you have the server software/licence, you should also have access to the SQL Server data migrator (DTS, as Paul pointed out). Ask your DBA. It will be easier than implementing a loader yourself. It's hard to create a better version of something that is peforming well, all on your own.

OCUP UML fundamental and ITIL foundation
youtube channel
Sasikanth Malladi
Ranch Hand

Joined: Nov 04, 2000
Posts: 175
No, I do not intend to implement my own version of jdts, which considering my task is a little silly.

What I'd like to see is an ability to do a bulk insert, say inserting 1000 records at a go, instead of one at a time. I'm assuming that such an option is possible and will be more efficient than a sequential insert.

Thanks,
Sashi
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Sasikanth Malladi wrote:No, I do not intend to implement my own version of jdts, which considering my task is a little silly.

What I'd like to see is an ability to do a bulk insert, say inserting 1000 records at a go, instead of one at a time. I'm assuming that such an option is possible and will be more efficient than a sequential insert.

Thanks,
Sashi

Not jTDS, DTS.

How do you insert into a relational database without using an insert statement per row? What call will you make to SQL Server instead? Were you hoping to use "select into" for example?
Sasikanth Malladi
Ranch Hand

Joined: Nov 04, 2000
Posts: 175
I work with Oracle all the time and am a total newbie to MS SQL Server, added to which I don't have a command line tool.
As mentioned, my method of using a generic front end is expensive as it's inserting each record as a new insert.
Yes, an 'INSERT INTO SELECT' could be better (though I haven't tried it).
Does this insert a bunch of rows at a time?
For example, is an insert into select with 1000 records more efficient than 1000 insert into stmts?
Since I'll be reading off a text file, the select would simply contain the records as csv strings.
Thanks,
Sashi
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


As mentioned, my method of using a generic front end is expensive as it's inserting each record as a new insert.

If you use a generic front end you cannot avoid this without using some sort of proprietory SQL.

select into work with new tables only. You'll need something like the bulk insert procedure (check the docs) to avoid multiple inserts, though all that really does is switches most of the work onto the database.

If you are an Oracle person normally and are looking for the sort of thing you get with SQL Loader, DTS is probably what you want. Like Jan said, if you have the server you have DTS. I'd use that.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Server bulk insert
 
Similar Threads
MS SQL
Million record upload and diplay with pagination
Bulk data through JDBC
PrintWriter doesnt want to write all
JDBC for loading data into SQL Server