0
CREATE TABLE Customer
(
    CustomerID int identity(100,1) PRIMARY KEY,  
    CustomerName varchar(55) not null,
    contact int not null,
    [Address] text
)

INSERT INTO Customer (CustomerID, CustomerName, contact, [Address])
VALUES (101, 'Bruce Wayne', 0012124567890, '31, Wayne Manor Estate, Gotham');

I get this error:

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

SET IDENTITY_INSERT Customer ON

INSERT INTO Customer(CustomerID, CustomerName, contact, [Address])
VALUES (101, 'Bruce Wayne', 0012124567890, '31, Wayne Manor Estate, Gotham');

Now I get another error:

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sajithwi
  • 1
  • 2
  • 4
    Don't use an `int` to store phone numbers; despite their name they are *not* numbers. Also `text` has been deprecated since 2005 and shouldn't be used anymore either. I doubt, as well, that you need *over 8000 characters* to store an address either. Generally, it's advised to store an Address in parts as it's *far* easier to concatenate them than it is to try and split them into the correct respective parts. – Thom A Sep 19 '22 at 19:00
  • `Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.` I think the error says it all. Column with type `Identity` will have auto generated values so the first row you insert will have id 1, second row 2 and so on. You can not insert a value yourself for such a column though it may depend on the db or as the error suggests unless you change the `IDENTITY_INSERT` property. Check this link in case you want to toggle it https://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008 – long_hair_programmer Sep 19 '22 at 19:03
  • 1
    Check the OP's second attempt, @long_hair_programmer , they then enable `IDENTITY_INSERT` and the insert fails (because of their incorrect data type choice). – Thom A Sep 19 '22 at 19:05
  • Oh thanks @Larnu I missed that – long_hair_programmer Sep 19 '22 at 19:09
  • 4
    `12124567890` is greater than the max allowed `int` value of `2147483647` - but you should not use integer datatype for phone numbers anyway. Leading zeroes are significant for phone numbers but not integers. And phone numbers might have extensions or other non numeric aspects. – Martin Smith Sep 19 '22 at 19:12
  • As an example, When dialing an American number from outside of the USA, it might be shown as something like "+001 123 4566 789"; both the `+` and leading `0`'s are important. As a `int` the "+00" would be lost. – Thom A Sep 19 '22 at 19:16
  • What is the best data type to enter a phone number with minimum suitable data allocation ? – sajithwi Sep 19 '22 at 19:22
  • 1
    A `varchar`, @sajithwi , with a suitable length defined. 20 characters should be enough. I would, however, ensure you have suitable validation on your application end to stop people entering silly values; a problem that I am all too well aware of in at least one place I have worked. – Thom A Sep 19 '22 at 19:23

0 Answers0