0

I want to insert a data row into my table as name: DSKWOR00 , but I get this error :

String or binary data would be truncated.

so I turned ANSI_WARNINGS OFF and it inserted the row but the result wasn't that my values

here is my T-SQL :

1- Create My Table :

CREATE TABLE [dbo].[DSKWOR00](   [DSW_ID] [nvarchar](10) NULL,    [DSW_YY] [float] NOT NULL,    [DSW_MM] [float] NOT NULL,    [DSW_LISTNO] [nvarchar](12) NULL,    [DSW_ID1] [nvarchar](8) NULL,    [DSW_FNAME] [nvarchar](60) NULL,    [DSW_LNAME] [nvarchar](60) NULL,    [DSW_DNAME] [nvarchar](60) NULL,    [DSW_IDNO] [nvarchar](15) NULL,    [DSW_IDPLC] [nvarchar](30) NULL,    [DSW_IDATE] [nvarchar](8) NULL,    [DSW_BDATE] [nvarchar](8) NULL,    [DSW_SEX] [nvarchar](3) NULL,    [DSW_NAT] [nvarchar](10) NULL,    [DSW_OCP] [nvarchar](50) NULL,    [DSW_SDATE] [nvarchar](8) NULL,    [DSW_EDATE] [nvarchar](8) NULL,    [DSW_DD] [float] NULL,    [DSW_ROOZ] [float] NULL,    [DSW_MAH] [float] NULL,    [DSW_MAZ] [float] NULL,    [DSW_MASH] [float] NULL,    [DSW_TOTL] [float] NULL,    [DSW_BIME] [float] NULL,    [DSW_PRATE] [float] NULL,    [DSW_JOB] [nvarchar](6) NULL,    [PER_NATCOD] [nvarchar](10) NOT NULL, CONSTRAINT [PK_DSKWOR00] PRIMARY KEY CLUSTERED(    [DSW_YY] ASC,    [DSW_MM] ASC,    [PER_NATCOD] Asc ) ON [PRIMARY]) ON [PRIMARY] 

2- Insert Into a row in My Table :

INSERT INTO dbo.DSKWOR00
(DSW_ID,DSW_YY,DSW_MM,DSW_LISTNO,DSW_ID1,DSW_FNAME,DSW_LNAME,DSW_DNAME,DSW_IDNO,DSW_IDPLC,DSW_IDATE,DSW_BDATE,DSW_SEX,DSW_NAT,DSW_OCP,DSW_SDATE,DSW_EDATE,DSW_DD,DSW_ROOZ,DSW_MAH,DSW_MAZ,DSW_MASH,DSW_TOTL,DSW_BIME,DSW_PRATE,DSW_JOB,PER_NATCOD) 
VALUES 
('6773790024',1401,1,1,'80079349','¤ç¬ üóä','ö‘þõþîž','‘®¤þóä','1011','¢¥þ','0','¢¤õ','ü÷¤þ',' ñõ‘ä ¤þ¢õ',0,0,'0','0','0','0','0','0','0','0',0,'OZ0509','4430928460')

3-The error if I insert with ANSI_WARNINGS ON :

Msg 8152, Level 16, State 30, Line 3
String or binary data would be truncated.
--Note: My insert is correct I don't know why I'm seeing this error

4-So for fixing error I did this :

SET ANSI_WARNINGS OFF

INSERT INTO dbo.DSKWOR00
(DSW_ID,DSW_YY,DSW_MM,DSW_LISTNO,DSW_ID1,DSW_FNAME,DSW_LNAME,DSW_DNAME,DSW_IDNO,DSW_IDPLC,DSW_IDATE,DSW_BDATE,DSW_SEX,DSW_NAT,DSW_OCP,DSW_SDATE,DSW_EDATE,DSW_DD,DSW_ROOZ,DSW_MAH,DSW_MAZ,DSW_MASH,DSW_TOTL,DSW_BIME,DSW_PRATE,DSW_JOB,PER_NATCOD) 
VALUES 
('6773790024',1401,1,1,'80079349','¤ç¬ üóä','ö‘þõþîž','‘®¤þóä','1011','¢¥þ','0','¢¤õ','ü÷¤þ',' ñõ‘ä ¤þ¢õ',0,0,'0','0','0','0','0','0','0','0',0,'OZ0509','4430928460')

SET ANSI_WARNINGS ON

5- Finally My row will insert but it's not value I have inserted into the table !

---- My Values in Inser :  ('6773790034',1401,1,1,'80069349','¤ç¬ üóä','ö‘þõþîž','‘®¤þóä','1011','¢¥þ','0','¢¤õ','ü÷¤þ',' ñõ‘ä ¤þ¢õ',0,0,'0','0','0','0','0','0','0','0',0,'OZ0509','4430928444')
---- What did Insert :       6773790034  1401 1 1  80069349   ¤ç¬? ü??     ?‘???î?   ‘®¤???    1011  ¢¥?    0  ¢¤?    ü÷?           ??‘? ¤?¢?  0 0  0   0   0   0   0   0   0   0  0  OZ0509   4430928444
0

1 Answer 1

2

Check the table structure for the [DSKWOR00] table. I think you'll find that the length of one or more fields is NOT big enough to hold the data you are trying to insert. I suspect DSW_SEX

2
  • I think you're correct, because OP defined that column as NVARCHAR(3) but is trying to insert these 4 characters into it: ü÷¤þ.
    – J.D.
    Aug 18, 2022 at 21:24
  • thanks , I Fixed DSW_SEX and used N'' it solved, How you find out to suspect DSW_SEX ? Aug 19, 2022 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.