Need help for this SQL Query

Question

Need help for this SQL Query

Mary Sachet 41

Hi thank you @LiHongMSFT-4306 @Olaf Helper @Pio Virzi , I also have an issue with this other Query it keeps saying

Failed to execute query. Error: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

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

SSingh-MSFT 16,371 Reputation points Moderator

2022-11-09T08:39:46.717+00:00

Hi @Mary Sachet ,

Thanks for using Azure services.

Just checking in to see if the above answers helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

3 answers

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2022-11-09T08:39:46.717+00:00

Hi @Mary Sachet ,

Thanks for using Azure services.

Just checking in to see if the above answers helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 1

LiHongMSFT-4306 31,566

Hi @Mary Sachet

Error: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

You have 4 columns in the insert( [Number_of_bedrooms],[Number_of_Kitchens],[Number_of_livingrooms],[Unit_area] ). However you are passing 7 values: (200,109,3,1,1,1, SQUARE (110)),
As the error said, the number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Best regards,
LiHong

Mary Sachet 41 Reputation points

2022-11-08T02:36:13.673+00:00

I just solved that issue however now its saying

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 31,566 Reputation points

2022-11-08T02:52:41.303+00:00
Hi @Mary Sachet

Would you recommend only doing 1 at a time of values?

No, I mean you need to make sure that the number of values in the VALUES clause is equal to the number of columns in the INSERT statement.
For example, when want to Insert into 4 columns, you need to ensure 4 values in the VALUES clause:

Try this query:

INSERT INTO [Unit_Building]([Number_of_bedrooms],[Number_of_Kitchens],[Number_of_livingrooms],[Unit_area]) VALUES (200,109,3,1)

If the table you want to insert has only 7 columns, then you don't need to write all the column names after Insert, like this:

INSERT INTO [Unit_Building] VALUES (200,109,3,1,1,1, SQUARE (110)), (201,111,1,2,1,1, SQUARE (125))
LiHongMSFT-4306 31,566 Reputation points

2022-11-08T03:14:43.553+00:00

Hi @Mary Sachet

Error: Arithmetic overflow error for type tinyint, value = 12100.000000.

Regarding datatype tinyint, see this image:

However, in your Insert Values, you have this: , which is out of the range 0~255.

You could change the datatype of field [Unit_area] from tinyint to int.
Try this: ALTER TABLE Unit_Building ALTER COLUMN Unit_area INT;
Mary Sachet 41 Reputation points

2022-11-08T03:27:17.107+00:00

Where exactly do i put this ALTER TABLE Unit_Building ALTER COLUMN Unit_area INT;

Answer 2

Mary Sachet 41

This is how it looks like I have changed it around now its saying this

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

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));

LiHongMSFT-4306 31,566 Reputation points

2022-11-08T05:43:23.377+00:00
Mary Sachet 41 Reputation points

2022-11-08T05:47:07.293+00:00

Thank you now the Failed to execute query. Error: 'tinyint' is not a recognized built-in function name.

I will need to change it to find the right one

LiHongMSFT-4306 31,566

tinyint is not a function, it is a kind of Datatype.
You cannot use it like this:

Ok, first step, run this query: ALTER TABLE Unit_Building ALTER COLUMN Unit_area INT; If you have executed it, then turn to second step.
Second step, run this insert query:

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

Answer 3

Mary Sachet 41

where would I insert this ALTER TABLE Unit_Building ALTER COLUMN Unit_area INT;

LiHongMSFT-4306 31,566 Reputation points

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

Put the Alter clause before the Insert statement.
Mary Sachet 41 Reputation points

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

i did that now, now its saying

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.

ALTER TABLE Unit_Building ALTER COLUMN Unit_area INT;
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)),
(204,114,4,1,1, SQUARE (150)),
(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));
LiHongMSFT-4306 31,566 Reputation points

2022-11-08T06:25:36.173+00:00

All right, don't you find it is the same issue with your previous post issue?
How to solve this please

Share via

Need help for this SQL Query

3 answers

Your answer