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.


 

About Joseph Dantoni
A DBA, cyclist, cook (who likes to play chef occasionally)

One Response to Loading Data from SQL Server to Vertica

  1. dharmon says:

    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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,785 other followers

%d bloggers like this: