4

I am trying to execute the following code. However, I continue to recieve the following 'Missing semicolon (;) at the end of SQL statement error in Microsoft Access.

The first query creates the table with the columns defined.

create table test 
(
    ProcessID int, 
    Name varchar(10),  
    Address varchar(10),    
    RandomData varchar(10)
);

The second query is causing the missing semicolon error.

INSERT into test 
VALUES (123 , 'TestName', 'TestAdd', 'qwrj3ri'), 
       (456 , 'TestName2', 'TestAdd', 'qwerty'),    
       (789 , 'TestName', 'TestAdd', 'qwrj3ri'),    
       (1234, 'Testing123', 'tester', 'asdfghjk');
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
syavatkar
  • 85
  • 1
  • 1
  • 7
  • 3
    See the syntax highlighting? It points out your missing single quote next to the end of your insert. – Thomas Tschernich Jul 31 '15 at 14:54
  • 1
    I don't think access supports multiple rows in the `values`. – Damien_The_Unbeliever Jul 31 '15 at 14:54
  • 1
    `, T` superfluous character on end of first set of values & `sdfg');` - missing open quote. These are causing the issue / SQL's trying to guess the error but isn't intelligent enough to display the real error. Fix those issues and you're good. – JohnLBevan Jul 31 '15 at 14:55
  • 1
    In Access SQL, `INSERT ... VALUES` allows you to insert only one row, as Damien suggested. – HansUp Jul 31 '15 at 15:01
  • Despite correcting the errors. It continues to display the error. Thanks guys! Looks like I will have to enter the data manually. – syavatkar Jul 31 '15 at 15:05
  • Can you edit the question to include your amended code - we may spot something else. Also, is there code before or after that insert line; perhaps that's where the issue's lurking? – JohnLBevan Jul 31 '15 at 15:12

2 Answers2

7

Code with amendments per above comments to make it Access friendly & remove typos:

INSERT INTO test ( ProcessID, Name, Address, RandomData)
VALUES (123 , 'TestName', 'TestAdd', 'qwrj3ri');

INSERT INTO test ( ProcessID, Name, Address, RandomData)
VALUES (456 , 'TestName2', 'TestAdd', 'qwerty');    

INSERT INTO test ( ProcessID, Name, Address, RandomData)
VALUES (789 , 'TestName', 'TestAdd', 'qwrj3ri'); 

INSERT INTO test ( ProcessID, Name, Address, RandomData)
VALUES (1234, 'Testing123', 'tester', 'asdfghjk');

Useful reference: https://msdn.microsoft.com/en-us/library/bb243852(v=office.12).aspx

Specific comments:

@Damien_The_Unbeliever:

I don't think access supports multiple rows in the values.

Amended to include an insert into per row instead of a value set per row (values (...), (...)).

@Thomas Tschernich:

our missing single quote next to the end of your insert

Changed 'tester', sdfg') to 'tester', 'sdfg');

@JohnLBevan:

superfluous character on end of first set of values

Changed 'qwrj3ri'), T to 'qwrj3ri'),

JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • Thanks John. I think its because I was not familiar with access sql. I actually had first written the code in mysql as I was mroe familiar with it and tried to translate it into sql code for access. Basically I was making a test file and running sql queries before actually importanting an excel document where I could work with the real data. Unfortunately, I am not sure I am able to translate all my mysql queries sucessfully. – syavatkar Jul 31 '15 at 15:18
  • No worries; I'm familiar with avoiding Access / tend to write my code in T-SQL (SQL Server) then translate from working code. – JohnLBevan Jul 31 '15 at 16:07
4

You can insert multiple rows in one insert statement in SQL server,but in MS ACCESS its not possible as above listed.

More techniques on multiple inserts in access are described beautifully here

Community
  • 1
  • 1
Tharif
  • 13,794
  • 9
  • 55
  • 77