Re: How to export data from SQL Server to Vertica
Posted: Thu Jul 24, 2014 8:19 pm
I ended up writing a VB program to look for 'NULL|', '|NULL' or '|NULL|' and replace with | or || depending.
sqlcmd -S MYSERVER -d mydb -u -s"|" -I -h-1 -k2 -W -i "C:\path\input.sql" |null-program.exe | vsql -h verticadev-d dbase-U dbadmin-w password-c "COPY DEV.myTable FROM LOCAL STDIN DELIMITER '|' REJECTED DATA 'C:\path\Reject.txt' EXCEPTIONS 'C:\path\Except.txt';"
Loaded 1 million rows, 1.5 GB of raw data in 71.70 minutes. The replace piece probably adds quite a bit of processing time, but this solution has taught me quite a lot.
I appreciate all the help.
sqlcmd -S MYSERVER -d mydb -u -s"|" -I -h-1 -k2 -W -i "C:\path\input.sql" |null-program.exe | vsql -h verticadev-d dbase-U dbadmin-w password-c "COPY DEV.myTable FROM LOCAL STDIN DELIMITER '|' REJECTED DATA 'C:\path\Reject.txt' EXCEPTIONS 'C:\path\Except.txt';"
Loaded 1 million rows, 1.5 GB of raw data in 71.70 minutes. The replace piece probably adds quite a bit of processing time, but this solution has taught me quite a lot.
I appreciate all the help.