How to fix this SQL Query

Mary Sachet 41 Reputation points
2022-11-08T02:43:01.91+00:00

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.
@CosmogHong-MSFT @Olaf Helper

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,605 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,545 questions
0 comments
Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2022-11-08T06:04:54.49+00:00

    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

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 111.5K Reputation points
    2022-11-08T05:20:33.157+00:00

    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.

    0 comments
  2. Mary Sachet 41 Reputation points
    2022-11-08T05:22:44.3+00:00

    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'.

    0 comments