5

I've got a table like this:

CREATE TABLE Customers
(CustomerID int Identity (1,1) NOT NULL PRIMARY KEY,
customerName varchar (50),
Address varchar (255),
Phone int NOT NULL,
Email varchar,
Gender varchar,
Age int, 
);

I tried inserting into this table like this:

Insert into Customers (customerName, Address, Phones, Email, Gender, Age)
Values ('Anosa Seunge', 'Keskuskatu 200', 358-3-4180, 'ijiosd@ao.com', 'Male', 19),
        ('Jihad Christian', '305 - 14th Ave. Suite 3B', 358-1-3688, 'jihado@ao.com', 'Female', 29);

and got this error:

Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.
linkonabe
  • 661
  • 7
  • 23
  • Do you want us to tell us which of the values is longer than the field you're trying to put it into? – John Dvorak Sep 15 '16 at 13:26
  • @JanDvorak actually, this is a good question since none of them seem to be at first glance. – ivan_pozdeev Sep 15 '16 at 13:35
  • 4
    there is a mismatch: you create `Phone` but try to fill `Phones`. Are these the actual commands? – Breeze Sep 15 '16 at 13:38
  • 5
    Pretty sure `358-3-4180` will be interpreted as `-3825` since the phone number is an int field. You also never specified a length for the Email or Gender `VARCHAR` fields which makes their length 1. – Kidiskidvogingogin Sep 15 '16 at 13:38
  • 2
    You didn't specify a length for the varchar columns email and gender. This means it will default to 1 and your email is longer. ALWAYS specify the length of varchar columns. – Sean Lange Sep 15 '16 at 13:39
  • 4
    Also, storing a phone number as an `int` is highly dubious. You're never going to want to add phone numbers together or perform other maths on them. Almost always, they should be stored as a string (with appropriate check constraints if you believe they should only contain digits) – Damien_The_Unbeliever Sep 15 '16 at 13:46
  • Thanks for all your comment. @Kidiskidvogingogin u are right, I actually got -3825. am trying to rectify it by altering the table but i couldnt. – linkonabe Sep 15 '16 at 19:40

1 Answers1

5

Your Email and Gender fields have a length of 1, which might be what you want for Gender (although probably not based on your insert statement) but is surely not for Email.

You want something like:

CREATE TABLE Customers
(CustomerID int Identity (1,1) NOT NULL PRIMARY KEY,
customerName varchar (50),
Address varchar (255),
Phone int NOT NULL,
Email varchar(50),
Gender varchar(50),
Age int, 
);
Mike L
  • 4,693
  • 5
  • 33
  • 52
  • Really? I thought `varchar` without size is equivalent to `varchar(255)`. – ivan_pozdeev Sep 15 '16 at 13:40
  • 1
    @ivan_pozdeev Sadly no, In some DBMS they have a default value that is higher, but in SQL server it would default to 1 – Takarii Sep 15 '16 at 13:40
  • 2
    Even more ridiculous is the default length changes depending on usage. For columns and variables it is 1, but parameters is 30. Talk about confusing....the easy solution is to be explicit. – Sean Lange Sep 15 '16 at 13:50
  • @SeanLange - no, not for parameters. It's 1 everywhere except [`CAST` and `CONVERT`](https://msdn.microsoft.com/en-gb/library/ms176089.aspx). But I agree that it's ridiculous. – Damien_The_Unbeliever Sep 15 '16 at 14:03
  • Related: http://stackoverflow.com/questions/2241238/why-does-oracle-varchar2-have-a-mandatory-size-as-a-definition-parameter/2244926#2244926, http://stackoverflow.com/questions/359257/why-is-30-the-default-length-for-varchar-when-using-cast – ivan_pozdeev Sep 15 '16 at 14:05
  • @Damien_The_Unbeliever see even people who know it still get it wrong because it is so unbelievably dumb. :) – Sean Lange Sep 15 '16 at 14:07