SQL error for creating table and inserting values in it. Error -12233 and 12101

Question

So I'm trying to create a table in SQL and then insert values into it. However, I seem to be getting this error:

[Error Code: -12101, SQL State: 42000] Syntax error, 'CHECK' assumed missing

and

[Error Code: -12233, SQL State: 42000] The number of insert values is not the same as the number of object columns

Here is my SQL code:

CREATE TABLE Server(
Nummer INTEGER NOT NULL
PRIMARY KEY(Nummer) 
);
INSERT INTO Server(Nummer)
VALUES (1,2,3,4,5);

So I want to create a table named Server which has a primary key named nummer. Nummer then has the values 1,2,3,4,5

UPDATE--------------------------------------------------------------------

So my new code is:

CREATE TABLE Server(
    Nummer INTEGER NOT NULL,
    PRIMARY KEY(Nummer), 
);

INSERT INTO Server(Nummer)
    VALUES (1);
INSERT INTO Server(Nummer)
    VALUES (2);
INSERT INTO Server(Nummer)
    VALUES (3);
INSERT INTO Server(Nummer)
    VALUES (4);
INSERT INTO Server(Nummer)
    VALUES(5);

I solved the check problem by simply putting a comma after every statement in the create section.

But I got a new problem which is this error code:

[Error Code: -12101, SQL State: 42000] Syntax error, IDENTIFIER IDENTIFIER assumed missing

Your added `CREATE TABLE` statement has an extra comma just before the final `)`. Remove it! — jarlh, Jun 30 '17 at 12:13

score 1 · Accepted Answer · answered May 18 '17 at 16:25

1

You have a table with a single colums so you can use multiple insert as

 INSERT INTO Server(Nummer)
 VALUES (1);
 INSERT INTO Server(Nummer)
 VALUES (2);
  .....

OR If you want batch insert you should use this way

 INSERT INTO Server(Nummer)
 VALUES (1),(2),(3),(4),(5);

answered May 18 '17 at 16:25

ScaisEdge

131,976
10
91
107

I appreciate the quick answer. It fixed the second error but the first error remains. Now I only get this error: [Error Code: -12101, SQL State: 42000] Syntax error, 'CHECK' assumed missing. What does this really mean and any ideas on how to fix it? – JubbeM May 18 '17 at 16:35
I never see an Error Code: -12101, which db you are using ? seems not related to the code you provided .. you have other .. – ScaisEdge May 18 '17 at 16:47
the -12101 is sintax error for assuminng missing token.. but your (SQL) code seems right to me .. check for mimer-sql specified .. http://www.it.uu.se/research/group/udbl/dbt-ht2004/sql_programmers_manual_82.pdf – ScaisEdge May 18 '17 at 17:05
Mimer SQL doesn't support that batch INSERT syntax. I.e. one INSERT per value is required. – jarlh Jun 30 '17 at 11:57
Yes, I know... My comment was intended as information for other users! – jarlh Jun 30 '17 at 12:04
@jarlh you are not the OP so you should post your question and not .. manage the question of others users – ScaisEdge Jun 30 '17 at 12:06
Your answer, which is accepted, contains two different `INSERT` versions. One of them works fine with Mimer SQL, but the other doesn't. Why am I not supposed to write a comment telling that Mimer SQL doesn't support the batch SQL syntax? – jarlh Jun 30 '17 at 12:34

score 0 · Answer 2 · answered May 18 '17 at 16:32

0

You can't insert a list of values like that. You have to create 5 insert clauses. If you're using Sql server, you can create one insert clause, with five values.

answered May 18 '17 at 16:32

Renato Afonso

654
6
13

Insert multiple rows with a single statement is nothing special to SQL Server - it's part of the SQL standard – May 18 '17 at 16:33
you can't make an insert into table (column) values (1),(2) in Oracle. – Renato Afonso May 18 '17 at 16:35
You are right Oracle, does not support that. But essentially every other DBMS supports that - it's not just SQL Server and Oracle out there. – May 18 '17 at 16:36

SQL error for creating table and inserting values in it. Error -12233 and 12101

2 Answers2