CODEWITHSUNDEEP
sqlinsert
user4217271
user4217271

Reputation:

SQL Server 2008 insert Error

CREATE TABLE GroovyExps_Src
      (EMPNO       VARCHAR Primary Key,
       FIRSTNAME   VARCHAR NOT NULL,
       MIDINIT     VARCHAR NOT NULL,
       LASTNAME    VARCHAR NOT NULL,
       SALARY      VARCHAR NOT NULL,
       BIRTHDATE   VARCHAR NOT NULL,
       HIREDATE    VARCHAR NOT NULL,
       JoinTime    VARCHAR NOT NULL)

insert into GroovyExps_Src 
values('000010', 'CHRISTINE', 'I', 'HAAS', '52750', '1980-08-22', '2014-08-22 10:00:00.000000', '16:00')

Error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated. The statement has been terminated.

What's wrong??

Upvotes: 1

Views: 45

Answers (4)

SonalPM
SonalPM

Reputation: 1337

When You specify only varchar, its Default size is 1.. So You may not insert proper data.. You need to mention something as varchar(50) so that It will have enough Size to save data..

Upvotes: 0

Ajay2707
Ajay2707

Reputation: 5798

By default "Varchar" datatype, define a single character value, if you not define any size.

So give or define the maximum size in varchar data-type to accept input data as @mark suggest.

When you give more than a single value to insert or update in that column, it gives the same error.

Even though, if you define varchar(100) means character accepted length should be 100 and when you try to insert or update the 101 length data, the same error you get.

The reason is Sqlserver not insert beyond the maximum limit which you define or default length( 1 character).

Upvotes: 0

Greg the Incredulous
Greg the Incredulous

Reputation: 1836

VARCHAR will default to length of 1 unless you explicitly state what it should be. All the data you are trying to insert will be truncated to lengths of one character using your existing code.

Upvotes: 1

marc_s
marc_s

Reputation: 754250

You need to define a length for your varchar columns - otherwise, they will default to a length of 1 character!

CREATE TABLE GroovyExps_Src
    (EMPNO VARCHAR(25) Primary Key,
      ..... and so forth....

Also: is varchar really the most appropriate datatype for everything?? If you have a empno - this sounds like a number - so you should use an INT or DECIMAL(p,s) - not a Varchar!!

Things like HireDate or BirthDate should also clearly be DATE columns - again: don't just use Varchar for everything out of lazyness - think about your data and pick the most appropriate datatype for each column!

Upvotes: 4

Related Questions