BULK INSERT from a different server

Question

I am using T-SQL with SQL Server 2012.

Here is my question: I have a text file on server 10.10.10.1. I want to create a temporary table on server 10.10.10.2 from the previous text file.

According to my research, BULK INSERT works on local C: directory.

Is there a way to do the following?

BULK INSERT dbo.#G2
FROM '10.10.10.1.C:\File\textfile.txt'
WITH
(
  CODEPAGE = '1252',
  FIELDTERMINATOR = ';',
  CHECK_CONSTRAINTS
)

Thank you for your help.

The SQL Server process can read from any **local** directory on the machine it's installed on, or from any **UNC path** in the network it's attached to where the user account the SQL Server process is running under has (at least read) access to. So if you can create a Windows share on your second server and grant the SQL Server user account read access to it, you should be able to do this — marc_s, Dec 03 '17 at 08:20

score 1 · Accepted Answer · answered Dec 03 '17 at 08:22

You can read data from a file share (\\computer\share\folder\file) but the SQL Server process has to have access. As SQL Server generally runs as a local service account it can only access shares that allow anonymous access (so anyone can read the content of the share).

Better up upload to a folder on the server, but of course that means sharing a writable folder from the database server. While controllable (eg. dedicated partition, controlled ACL) it is still not ideal.

BULK INSERT from a different server

1 Answers1

Linked