Bulk Insert from a CSV file to a table in a remote server

Question

I use SQL server 2012 and windows powershell. I try to do bulk insert from a local CSV file to a table in a remote server. I tried to combine bulk insert with powershell script by using sqlcmd to connect to that server to execute the sql statement. I come up with

$path= "C:\UT\testdata.csv"
$customerSizeQuery = "BULK INSERT dbo.test FROM '$path' WITH (FIELDTERMINATOR =',',     ROWTERMINATOR ='\n');"
# $server and $database is fit as the exact server and database name in my real script
SQLCMD -W -S $server -d $database -h-1 -Q ("$customerSizeQuery") 

obviously it does not work since path is my local path.

I then tried to put the file content to a variable but it still have error. It seems to say $var is not a table

$path= "C:\UT\testdata.csv"
$var= get-content $path
$customerSizeQuery = "BULK INSERT dbo.test FROM $var WITH (FIELDTERMINATOR =',',     ROWTERMINATOR ='\n');"
# $server and $database is fit as the exact server and database name in my real script
SQLCMD -W -S $server -d $database -h-1 -Q ("$customerSizeQuery")

I am a beginner in sql and powershell and does not have an idea how to do it.

Is there any body having any suggestions?

Thanks so much!

Solution 1

If you have the SQL client tools installed on your local machine why not just use the bcp utility?

$path = 'C:\UT\testdata.csv'
$outFile = 'C:\UT\bcpOut.txt'
bcp -W -S $server -d $database -i $path -o $outFile -t, -T

See this MSDN page for details of the bcp command.

Solution 2

The BULK INSERT command requires that you provide a data file where the SQL Server instance can access it (see http://msdn.microsoft.com/en-us/library/ms188365.aspx). To my best knowledge, there is no way to specify the contents of the file as BULK INSERT input (as you tried in your second attempt).

If your server sees your client over the network, you can try specifying the CSV file using UNC, e.g. \\client\UT\testdata.csv. Alternatively, you can copy the CSV file to a network location that is seen by the SQL Server.

Comments

[Mark Z.]

6 years later, need to specify IN, e.g. (in cmd not PS): bcp table_name IN %path% -t, -c -S %sqlserver% -d %sqldb% -T -o %logpath% -e %errorlogpath%