CODEWITHSUNDEEP
sql-server
Kalpana
Kalpana

Reputation: 21

When Query is executed, it displayed an error in SQL server

I created a table and added values to the table. While I was adding values in one of the field 'HomePhone', I gave data value as 'null'. After that, I executed the query its showed the error. Later, I decided to leave the data as blank by coding as ' '.

The error message displayed as

"String or binary data would be truncated. The statement has been terminated."

    /* Creating table6 Customer */
create table Customer
(
    CustomerID              int primary key identity(301,1) not null,
    AccountID               int not null,
    CustomerAddress1        varchar(30),
    CustomerAddress2        varchar(30),
    CustomerFirstName       varchar(30),
    CustomerMiddleInitial   char(1),
    CustomerLastName        varchar(30),
    City                    varchar(20),
    St_ate                  char(2),
    ZipCode                 char(10),
    EmailAddress            varchar(40),
    HomePhone               char(10),
    CellPhone               char(10),
    WorkPhone               char(10),
    SSN                     char(9),    
    UserLoginID             smallint not null
);

/* Adding 5 row values to table6 Customer */

insert into Customer(AccountID,CustomerAddress1,CustomerAddress2,CustomerFirstName,CustomerMiddleInitial,CustomerLastName,City,
                    St_ate,ZipCode,EmailAddress,HomePhone,CellPhone,WorkPhone,SSN,UserLoginID)
values
    (102,'456 DEF Street','Unit 12','Williams','Kevin','George','London','ON','M5D1B3','[email protected]',' ','3366991100','1234567890','456789012',2),
    (103,'789 PQR Street','Unit 20','Lindsay','Steffi','Davidson','Seattle','WA','98128','[email protected]','6547893210','3366991100',' ','876543219',3);

I expect the query to be executed without error.

Upvotes: 0

Views: 76

Answers (1)

PSK
PSK

Reputation: 17943

Error message clearly explains that one of the value you are trying to insert is bigger than the length of column you have defined in your table.

In your case you have defined CustomerMiddleInitial as char(1), so you are not supposed to enter more than one character to this column value, but you are trying to insert following values.

Kevin
Steffi

Either alter the table to increase the column length or specify only one character to column CustomerMiddleInitial

Upvotes: 1

Related Questions