-1

I have a Microsoft SQL Server 2017 database on Google Cloud. I'm trying to bulk load CSV files from my local machine to the database, using the following command

BULK INSERT umls.mrxw_spa
FROM 'C:\data\umls\2020AA\META\MRXW_SPA.RRF'
WITH (FIELDTERMINATOR = '|', BATCHSIZE = 10000);

but I get an error message

Cannot bulk load. The file "C:\data\umls\2020AA\META\MRXW_SPA.RRF" does not exist or you don't have file access rights.

I don't know how to give the GCP SQL Server permissions to my local folder. This database used to be on Azure and it was quite easy to load, but the company changed its licensed and moved to Google Cloud. I know I could create a python script to load the data - but using the above command is a lot easier.

Has anyone been able to use the Bulk Insert in the GCP?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yardena
  • 1
  • Are you referring to SQL Server on Cloud SQL? Or to a SQL Server running in a Compute Engine VM? – JC98 Aug 17 '20 at 16:01

1 Answers1

0

You do not. The standard approach for bulk load is to move the data onto the server and load from there. IF you would have a proper network in place.... you would still have to use a UNC form (\machine\fileshare\path\file).

Basically your bulk insert command is sent to the server. THe server then looks for the file at c:\data.... (basically the path). This command is NOT EXECUTED ON YOUR COMPUTER. No SQL is. They all are sent to the server.

Giving the server permissions on your local machine is not an option. Besides that being tricky without domain - the server will likely NOT BE ABLE TO REACH YOUR MACHINE because it is behind NAT. So, your question is akin to "how can I bypass he secretary on (whatever ceo) office?" and then not even having the phone number of his office. Unless you have a network setup that allows the server to reach you (99.9% chance you do not have that in place) the permissions are useless to start with.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • So if I'm understanding you correctly - I need to move the csvs onto GCP and then use the same command to read the files from that location? – yardena Aug 16 '20 at 10:15
  • That is sort of standard. Especially if your machine is a typical user setup (i.e. not connected by a site to site or similar style vpn) and the server can not reach it - you must put the data in a place that is reachable. The bulk load is a ocmmand to the server, the server must be able to reach it, with the user the server process is running under. – TomTom Aug 16 '20 at 10:25
  • Do I need to do anything special on the SQL Server instance? I know when I ran this on Azure and I loaded the files into a blob I had to run a command to give the server access to the blob- is it the same here? If yes, what command do I need to write? – yardena Aug 16 '20 at 10:32
  • You really must of misunderstood my question. I'm not asking how to teach me everything, I'm just asking a basic question of how to load the csv files using bulk insert into the sql server database on GCP. I used to load data into sql server when it was on Azure with no problems and I'm just wondering how to do it on GCP. – yardena Aug 16 '20 at 10:45