0

Given a set of data and trying to insert all of the rows into the table it seems c-treeACE throws me an error message saying its a syntax error and highlights the zipcode here is an example: here is the script:

INSERT INTO testdata VALUES 
    ('1ZE83A545192635139','2018-06-19 00:00:00','MID-ATLANTIC SETTLEMENT SERVICES','10 NORTH PARK DRIVE','SUITE 100',NULL,'HUNT VALLEY','MO   ',210301876),
    ('1Z88Y9147852827763','2018-06-19 00:00:00','PROMETRIC - DISTRIBUTION','7941 CORPORATE DR',NULL,NULL,'NOTTINGHAM','ND   ',212364925),
    ('1Z88F3X58790349173','2018-06-19 00:00:00','STEPH YEAG','11333 MCCORMICK RD','MD5-031-05-04',NULL,'HUNT VALLEY','HG   ',21081),
    ('1Z5654132394463912','2018-06-19 00:00:00','KIMB  RE','6384 BLAIR HILL LN','PO BOX 10487',NULL,'BALTIMORE','JK   ',21209);
    ('1Z9Y53832934210246','2018-06-19 00:00:00','Crys random','4 BUCHANAN RD',NULL,NULL,'BALTIMORE','KL   ',21212);

The above is the script I run in C-tree and it says the syntax error is after the first entry which is

 ('1ZE83A500789635139','2018-06-19 00:00:00','MID-ATLANTIC SETTLEMENT SERVICES','10 NORTH PARK DRIVE','SUITE 100',NULL,'HUNT VALLEY','MO   ',**210301876),** <---- this where the error highlights.

I assume in ctree its only able to read one at a time because thats when it works is when I put one entry at a time. Mind you I am getting this data from a csv file but I don't know how to read data from a csv file and import into ctreeACE unless someones knows how to do that then I am all ears and that would save me a lot of time!

Bartholomew Allen
  • 21
  • 5
  • I don't see an obvious syntax error in the first line, but you have a `;` at the end of the 4th row of values – Aaron Dietz Jun 27 '18 at 14:21
  • That's where I am confused its pointing to the end of the first entry which I assume is saying it can only process one line at a time in ctree – Bartholomew Allen Jun 27 '18 at 14:23

1 Answers1

0

I find this strange, but apparently in c-treeACE you can only insert one row at a time using the INSERT...VALUES approach.

From the documentation:

To insert more than one row, an insert statement with a subquery must be executed. The following sample code shows insertion of rows from the table customer into a table ny_customer.

Code example:

INSERT INTO ny_customer (CUST_NO, name, street, city, state)

SELECT  CUST_NO, name, street, city, state
FROM    customer
WHERE   state = 'NY'

Source

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • I've seen this example before but doesn't this only insert into columns where the State is NY? – Bartholomew Allen Jun 27 '18 at 14:34
  • @BartholomewAllen It would insert all data from the table `customer` where the state is NY, into the table `ny_customer`. Whatever returns from the select is what is inserted into the destination table. – Aaron Dietz Jun 27 '18 at 14:49
  • I know but I my case I am not inserting data from one table to another I am trying to insert data from a csv file into the testdata table @Aaron Dietz – Bartholomew Allen Jun 27 '18 at 14:57
  • @BartholomewAllen I was just answering the question of why your insert wouldn't work with > 1 row... You should start another question asking how to import a csv into c-tree (I don't know that myself) – Aaron Dietz Jun 27 '18 at 15:16
  • Thank you Aaron Dietz I appreciate the effort and you answersing this question :) and I did yesterday lol – Bartholomew Allen Jun 27 '18 at 15:18