t-sql 2012 update statement truncates varchar value

Question

t-sql 2012 update statement truncates varchar value

wendy elizabeth

SSCoach

Points: 16915
More actions
October 7, 2015 at 8:49 pm

#312075

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:
1.
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
2.
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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post create table statements for all involved tables please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246693 More actions · Answer 2

If you could post an example in consumable format, which others can run, it would be even better.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

What do you see when you stare & compare?

SELECT

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

--AND LKC.combo <> lockCombo1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden