CODEWITHSUNDEEP
sqlsql-serverpowershell
Picard
Picard

Reputation: 51

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

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!

Upvotes: 5

Views: 20709

Answers (3)

Be Champzz
Be Champzz

Reputation: 929

This works for me.

[Assuming the file is accessible from the server]

BULK INSERT AppStats
FROM '\\machine_full_domain\path\DUMMY.csv'
WITH 
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)

Upvotes: 0

Bruce
Bruce

Reputation: 1623

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.

Upvotes: 5

PeterK
PeterK

Reputation: 3817

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.

Upvotes: 2

Related Questions