Loading Data from SQL Server to Vertica

Spread the love

I’m in the midst of learning Vertica—and I’m trying to load some AdventureWorks data into tables. I came across this blog post from Doug Harmon (b) where he mentions using sqlcmd and a batch file to transfer data.

Create a Windows Batch file, XferData.bat, to transfer the data

sqlcmd -S <server/instance> -d <database> -u -s”|” -I -h-1 -k2 -Q “EXEC Export_Data ” | ^

vsql -h <host> -d <database> -U <username> -w <password> -c “COPY public.DH_StageTable FROM LOCAL STDIN DELIMITER ‘|’ ;”

The batch file can be called from a SQL Server Agent job using either CmdExec or PowerShell.”


Unfortunately the comments for Doug’s blog seem to be broken, so I had to blog this myself. When I attempted to run that code as a batch file, Windows threw the following error:




C:\Temp>sqlcmd -S localhost -d AdventureWorks2012 -u -s”|” -I -h-1 -k2 -Q “EXEC Export_Data ” |

‘ ‘ is not recognized as an internal or external command, operable program or batch file.


When I tried to run it in PowerShell, Powershell wasn’t happy about the caret:


PS C:\temp> .\test_sql_Vertica.ps1

^ : The term ‘^’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the

spelling of the name, or if a path was included, verify that the path is correct and try again.

At C:\temp\test_sql_Vertica.ps1:1 char:89

+ … Export_Data “| ^

+ ~

+ CategoryInfo : ObjectNotFound: (^:String) [], CommandNotFoundException

+ FullyQualifiedErrorId : CommandNotFoundException


So the solution?


sqlcmd -S localhost -d AdventureWorks2012 -u -s”|” -I -h-1 -k2 -Q “EXEC Export_Data “|

vsql -h -d AdventureWorks_Practice -U dbadmin -w Anex1 -c “COPY public.Practice_StoredProc FROM LOCAL STDIN DELIMITER ‘|’ ;”


PS C:\temp> .\test_sql_Vertica.ps1

Rows Loaded



(1 row)


Don’t forget to add VSQL.exe to your path in Windows, as well.


2 thoughts on “Loading Data from SQL Server to Vertica

  1. dharmon

    Glad to see you are exploring Vertica! I hadn’t tested powershell and I’m glad you have found a solution for powershell. I’d like to help debug why the DOS bat file did not work. The ^ symbol at the end of the line tells DOS that the line continues below on the next line. By any chance do you have a blank line in the middle of the bat file or is there a space after the ^? There should be only two lines in the file. Alternatively, you could put everything on one line and it should work. Take out the ^ if you put everything on one line.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.