Skip to main content

Issues importing data from SQL Server to Vertica using Toad Data Point

  • December 19, 2016
  • 4 replies
  • 19 views
DBDave
Forum|alt.badge.img
  • DBDave
  • Participating Frequently

Hello,

 

I am trying to import a table from SQL Server into Vertica using Toad Data Point. Most of the data loaded but I have several rejected rows. I assume the rows are rejected because of comma delimited lists. Our data has the following delimeters in some of the data... comma, pipe, colon, semicolon, etc. So this import is becoming difficult.

 

It is my understanding that when you import data into Vertica, Vertica translates the data into a CSV file and then loads the CSV. We are eventually going to use Informatica to import the data, but as of now all I have is Toda DP.

 

Steps: Used Import/Export wizard from Toad to load data from a query. As I said, most of the data loads properly with the exception of  data that has commas.

 

Any help would be appreciated. Please let me know if I am being clear enough about this topic.

    4 replies

    DBDave
    Forum|alt.badge.img
    • DBDave
    • Author
    • Participating Frequently
    • December 19, 2016

    Thanks for your response. I guess no one is working today. This forum is a joke. It's been several hours since I posted this and no response. Terrible.

    ckotsidimos
    Forum|alt.badge.img+1
    • ckotsidimos
    • Participating Frequently
    • December 20, 2016

    Having done this before, I would suggest to use a delimeter like { or } or [ or ] or.... , something that is not used in your data. It was the only way I found and worked correctly as vertica can understand any character as a delimeter. Furthermore, with the export/import wizzard of sql server you can export the dta directly to csv and then use the copy command toimport it.

    That is onllly from my experience

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • January 3, 2017

      Woww , hold your horses buddy !!! 

     

    This is not payed support ! is pure community forum ! 

     

     

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • January 3, 2017

    Here is simple solution and is free

     

     Use the sqlcmd tool in Sql Server and pipe it into Vertica on csv file transfer etc.. (you need the vertica client installed in Sql Server box).

     

    For the delimiter issue use the \t tab delimited character.

     

    sqlcmd -S sql_server_ip,sql_server_port -d database_name -E -s"\t" -W 
    -Q "SELECT * FROM database.dbo.TABLE_NAME"|
     vsql -h 10.1.43.98 -d db_name -U db_user -w passwd 
    -c"copy  schema.TABLE_NAME FROM STDIN SKIP 1 DELIMITER E'\t' NULL
     as 'null' DIRECT STREAM NAME 'Loading schema.TABLE_NAME from Sql Server' 
     REJECTED DATA AS TABLE staging.TABLE_NAME_rej;"

     

      Make sure you have it on single line when running it.

     

    Note:

    See the rejected table for rejects and once oyu have them in Vertica they are eassy to be treated in cleaned.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement