Is it possible to use sql server bulk insert without a file?

Question

Curious if this is possible: The app server and db server live in different places (obviously). The app server currently generates a file for use with sql server bulk insert.

This requires both the DB and the app server to be able to see the location, and it makes configuration more difficult in different environments.

What I'd like to know is: is it possible to bypass the file system in this case? Perhaps I can pass the data to sql server and have it generate the file?

I'm on sql server 2008, if that makes a difference.

thanks!

Answer 1

I don't think you can do that with SQL Server's bulkcp tool, but if your app is written using .NET, you can use the System.Data.SqlClient.SqlBulkCopy class to bulk insert rows from a data table (or any datasource you can access with a SqlDataReader).

Answer 2

From the documentation on bulk insert:

BULK INSERT 
   [ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ] 
      FROM 'data_file' 

The FROM 'data_file' is not optional and is specified as such:

'data_file' Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \Systemname\ShareName\Path\FileName. For example, \SystemX\DiskZ\Sales\update.txt.

Your application could do the insert directly using whatever method meets your performance needs.