T-sql 2012 update statement truncates varchar value

Transact-SQL
,
1

In t-sql 2012, data is obtained from [Inputtb].lockCombo1 where it is defined as varchar(8). The data is copied to test.dbo.LockCombination.combo where the field is defined as varchar(8). This copies all the data except the last right column.

Basically a value that is '12-34-56' intially from [Inputtb].lockCombo1 ends on in st.dbo.LockCombination.combo looking like

'12-34-5'. In this case the last value of '6' is missing. I have tried to use various string functions to obtain the entire value that should be '12-34-56' and ends up looking like '12-34-5'.

Here are 2 sqls that I have used and I get the same results:

UPDATE LKC
SET LKC.combo = lockCombo1
FROM [Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber =
@SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
and LKR.schoolID = @SchoolID
JOIN test.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN test.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
WHERE LKC.seq = 1

Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber LKR.number
and LKR.[schoolID] = @SchoolID
JOIN test.dbo.Lock LK ON LKR.lockID = LK.lockID
WHERE A.schoolnumber = @SchoolNumber
) AS LKC2 (lockID,seq,combo,schoolnumber)

ON
(
LKC1.lockID = LKC2.lockID
and LKC1.seq = 1 and LKC2.seq =1
)
WHEN MATCHED AND LKC2.schoolnumber = @SchoolNumber
THEN UPDATE SET LKC1.combo = LKC2.combo
WHEN NOT MATCHED
THEN INSERT (lockID,seq,combo) VALUES(LKC2.lockID,1,LKC2.combo);

**Note: I can not change the definition of the columns since these are production settings.

Thus can you should me modified sql that will end up with the entire value of 8 characters in the [Inputtb].lockCombo1 column?

2

It must be truncated somewhere.Possible cause is in the table column should have less length for the column.
Even try the below query and give feedback

UPDATE LKC 
 SET LKC.combo = cast(ltrim(rtrim(lockCombo1)) as varchar(8))
 FROM [Inputtb] A 
 JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = 
 @SchoolNumber 
 JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = 
 LKR.number 
     and  LKR.schoolID = @SchoolID 
 JOIN test.dbo.Lock LK ON LKR.lockID = LK.lockID 
 JOIN test.dbo.LockCombination LKC ON LK.lockID = LKC.lockID 
 WHERE LKC.seq = 1
1 Like