I have an application that uses MS SQL Server for which I'll need to do a bulk insert from a file. The sticking point is that the database and my application will be hosted on separate servers. What is the best way to do a bulk insert across a network? Two ideas I'd come up with so far:
From the app server, share a directory that the db server can find, and do the import using a bulk insert statement from the remote file
Run an FTP server from the db server - when the import is performed, simply ftp the file to the db server and do the import using a bulk insert from the local file (I am leaning towards this option).
Can anyone else tell me if there is a better way to do this, or if not, which one makes the most sense, and why?
I've done it before, and tried both options.
In the end, I did the opposite of choice 1. Share a directory on the DB server that the app can find. You don't have to deal with bandwidth issues during the bulk insert.
The FTP server option works if you're particularly concerned with security or transferability.
A final option (be very careful) is to use DTS with a localized SQL server. It might be more secure. If you do it wrong, it'll be much less efficient.
I'm still looking for a way to do this in MS SQL, but what I did in MySQL was to save the CSV as a BLOB in a temporary table, run SELECT ... INTO DUMPFILE in a directory local to the DB server, then run the regular LOAD DATA statement on that local file (EDIT: I feel I should point out, this was before LOAD DATA LOCAL was available).
I think spWriteStringToFile will do it.
EDIT: I'm on to something.
comm.CommandText = @"EXEC spWriteStringToFile @data, 'c:\datadumps', 'data.csv';
BULK INSERT my_table FROM 'c:\datadumps\data.csv';";
comm.Parameters.AddWithValue("data", File.ReadAllText(path));
comm.ExecuteNonQuery();
If the file is small enough then the ftp option may work (you will have a duplicate in your db box). However I don't see much of a problem to do option 1) if you have a gigabit network between the two with a single hop.
I am not sure about 2k08 as there are some additional utilities to copy files between servers. FTP will be the faster of the two, because it doesn't use the window's file system to transfer the file. (there is overhead, but unless the file is large it might be negligible). There are other advantages to not using a share, such as the remote server with the file crashing mid-access.
I disagree with cmartin about adding an open share to the db server. Generally you don't want to open file shares to the db server as it is generally considered a security risk, and a lot of places won't allow it. That being said it would negate you having transfer the file to another location to use the bulk import.
Actually you can place the file at both the Application and Database server.
Both files must be the same path. From the Application server, the purpose is only for selection. The Database server is for the bulk insert.
This is what I did for my customer and they also agreed with this simple trick.
