question

VAIBHAV avatar image
VAIBHAV asked

SQl insert statement

Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated. MY QUERY IS: `insert into department2 (department_id,department_name) values(1,'mec')` MY TABLE IS: create table department2 ( department_id int (10), department_name varchar (20), ) how do i remove this error?
sql-server-2008t-sql
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Håkan - yes that's why I don't believe that it is the actual DDL....
1 Like 1 ·
INT(10)? The INT datatype doesn't have a size
0 Likes 0 ·
ok hakan but if i remove that int size also.. then also it is giving me same error msg.. do u have any idea to remove this?
0 Likes 0 ·
The code works fine for me when I remove the size for the INT
0 Likes 0 ·
This is what i tried and it did work: create table #department2 ( department_id int , department_name varchar (20), ) insert into #department2 (department_id,department_name) values(1,'mec') SELECT * FROM [#department2] AS D
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Can you please execute this code and tell us the results ... IF OBJECT_ID('department2_test') > 0 DROP TABLE department2_test go CREATE TABLE department2_test ( department_id INT , department_name VARCHAR(20), ) go INSERT INTO department2_test ( department_id, department_name ) VALUES ( 1, 'mec' ) go SELECT [d].[department_id] , [d].[department_name] FROM department2_test AS d go DROP TABLE department2_test
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes i will try this
0 Likes 0 ·
thank you.. very much.. i got the expected output. but can u tell me that wat wrong in my code.?
0 Likes 0 ·
You probably had a trigger as Kev suggested or problem with schemas.
0 Likes 0 ·
ok..ok.. This is the first time of mine using this Q&A and i m very much satisfied by ur great response.. thanks for everything..
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
The error indicates that the data is too big for the field. Either the code you have posted is incorrect - as that INSERT on that table would be fine - or you have a trigger on the table that is trying to do something else here.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you drop and recreate the table?
1 Like 1 ·
As @Kev Riley states this message is because the data is too bit for the fields specified, where is the data coming from?
1 Like 1 ·
no the code i have posted is perfectly same as i have written in sql and if about the big data u can see here that for int i have specified value:1 and for varchar:mech, so i guess there should not be problem in that area.
0 Likes 0 ·
ok. i try this also.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Is the table created with the DBO schema and are your login assigned to the DBO as your default schema? If there are other tables with same name but different schema then you are depending on your default schema. Try to schema qualify your code.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.