1

I have been using ISQL (SQLAnywhere 12) to import data from CSVs into existing tables using INPUT INTO and never ran into a problem. Today I needed to import data into a table containing an auto-increment column, however, and thought I just needed to leave that column blank, so I tried it with a file containing only 1 row of data (to be safe). Turns out it imported with a 0 in the auto-increment field instead of the next integer value.

Looking at the Sybase documentation, it seems like I should be using LOAD TABLE instead, but the examples look a bit complex.

My questions are the following...

  • The documentation says the CSV file needs to be on the database server and not the client. I do not have access to the database server itself - can I load the file from within ISQL remotely?
  • How do I define the columns of the table I'm loading into? What if I am only loading data into a few columns and leaving the rest as NULL?
  • To confirm, this will leave existing data in the table as-is and simply add to it using whatever is in the CSV?

Many thanks in advance.

Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
sqlandrew
  • 13
  • 1
  • 4

1 Answers1

1

  1. Yes. Check out the online documentation for LOAD TABLE - you can use the USING CLIENT FILE clause.

  2. You can specify the column names in parens after the table name, i.e. LOAD TABLE mytable (col1, col2, col3) USING CLIENT FILE 'mylocalfile.txt'. Any columns not listed here will be set to NULL if the column is nullable or the equivalent to an empty string if it's not - this is why your autoincrement column was set to 0. You can use the DEFAULTS ON clause to get what you want.

  3. Yes, existing data in the table is not affected.

Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
  • Thank you so much. One follow up question - in the doc, some of the examples use filler() in the column list. What does that do? – sqlandrew Jul 13 '16 at 14:25
  • It's used when you have a csv file with more columns of data than you want to import. Say you have a file with 4 columns but you don't need the third one. You would use `LOAD TABLE mytable (col1, col2, filler(), col4) ...` to skip the third column. – Graeme Perrow Jul 13 '16 at 16:35
  • Got it. Thanks again! – sqlandrew Jul 14 '16 at 17:23