How to fix this SQL Query

Question

How to fix this SQL Query

Mary Sachet 41

INSERT INTO Unit_Building (Unit_Building_Number, Public_Housing_Number, Number_of_bedrooms,Number_of_Kitchens,Number_of_livingrooms,Unit_area)
VALUES
(200,109,3,1,1, SQUARE (110)),
(201,111,1,2,1, SQUARE (125)),
(202,112,2,3,1, SQUARE (130)),
(203,113,3,1,1, SQUARE (140)),
(204,114,4,1,1, SQUARE (150)),
(205,115,2,3,1, SQUARE (120)),
(206,116,1,1,1, SQUARE (180)),
(207,117,4,2,1, SQUARE (190)),
(208,118,3,1,1, SQUARE (175)),
(209,119,3,2,1, SQUARE (200));

Failed to execute query. Error: Arithmetic overflow error for type tinyint, value = 12100.000000.
The statement has been terminated.
@LiHongMSFT-4306 @Olaf Helper

Accepted answer

2 additional answers

Your answer

Answer 1

Ronen Ariely 15,206

Hi,

Failed to execute query. Error: Incorrect syntax near the keyword 'VALUES'.

Now you added a new tiny issue in your query which is not related to the original issue.

In your new query you missing closure of the brackets before the word 'VALUES`

INSERT INTO Unit_Building (Unit_Building_Number, Public_Housing_Number, Number_of_bedrooms,Number_of_Kitchens,Number_of_livingrooms, Unit_area VALUES

Should be:

INSERT INTO Unit_Building (Unit_Building_Number, Public_Housing_Number, Number_of_bedrooms,Number_of_Kitchens,Number_of_livingrooms, Unit_area) VALUES

Mary Sachet 41 Reputation points

2022-11-08T06:08:03.957+00:00

Thank you I have done that and now its saying this

Failed to execute query. Error: Violation of PRIMARY KEY constraint 'PK__Unit_Bui__346FDC6F9609B610'. Cannot insert duplicate key in object 'dbo.Unit_Building'. The duplicate key value is (204).
The statement has been terminated.

I also took of tinyint cause i was told it was wrong what am I supposed to put instead

INSERT INTO Unit_Building (Unit_Building_Number, Public_Housing_Number,Number_of_bedrooms, Number_of_Kitchens,Number_of_livingrooms, Unit_area)
VALUES
(200,109,3,1,1,(110)),
(201,111,1,2,1,(125)),
(202,112,2,3,1,(130)),
(203,113,3,1,1,(140)),
(204,114,4,1,1,(150)),
(204,114,4,1,1,(150)),
(204,114,4,1,1,(150)),
(205,115,2,3,1,(120)),
(206,116,1,1,1,(180)),
(207,117,4,2,1,(190)),
(208,118,3,1,1,(175)),
(209,119,3,2,1,(200));
Ronen Ariely 15,206 Reputation points

2022-11-08T06:18:35.827+00:00

You must learn to read errors :-)

The error you get is: Cannot insert duplicate key in object 'dbo.Unit_Building'. The duplicate key value is (204).

There is no way to present the issue simpler.

It is a simple English and clear error for anyone that know the basic of tabular databases. You cannot learn from trying and posting in the forum basic tasks.

Do you know what is a primary key?

I HIGHLY recommend to go over the following tutorial from start to end (It should not take more than full three days including testing the demo): https://www.w3schools.com/sql/

To understand what Primary KEY is, you can (NOT RECOMMENDED) to jump to the page regarding primary key: https://www.w3schools.com/sql/sql_primarykey.ASP

This is the first two sentences in the page:

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values...

Your issue is (as the error explicitly sys): you try to INSERT "duplicate key value" :-)

Answer 2

Viorel 122.6K

It seems that the Unit_area column is a tinyint, which is too small for a number like SQUARE(110), which is 12100.

Try recreating the table or column, using int, bigint or another appropriate type.

Answer 3

I changed it to tinyint and this is coming up

INSERT INTO Unit_Building (Unit_Building_Number, Public_Housing_Number, Number_of_bedrooms,Number_of_Kitchens,Number_of_livingrooms, Unit_area
VALUES
(200,109,3,1,1, tinyint(110)),
(201,111,1,2,1,tinyint(125)),
(202,112,2,3,1,tinyint(130)),
(203,113,3,1,1,tinyint(140)),
(204,114,4,1,1,tinyint(150)),
(204,114,4,1,1,tinyint(150)),
(204,114,4,1,1,tinyint(150)),
(205,115,2,3,1,tinyint(120)),
(206,116,1,1,1,tinyint(180)),
(207,117,4,2,1,tinyint(190)),
(208,118,3,1,1,tinyint(175)),
(209,119,3,2,1,tinyint(200));

Failed to execute query. Error: Incorrect syntax near the keyword 'VALUES'.

Share via

How to fix this SQL Query

2 additional answers

Your answer