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
|
1 2 3 4 5 |
$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
|
1 2 3 4 5 6 |
$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!
Answer:
If you have the SQL client tools installed on your local machine why not just use the bcp utility?
|
1 2 3 |
$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.