Loading Data from SQL Server to Vertica

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>test_sql_Vertica.bat

 

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 10.10.50.114 -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

————-

5

(1 row)

 

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