We help IT Professionals succeed at work.

LEFT 5 only populating 4 characters on INSERT

LouisvilleGeek
on
I am using the following statement in order to populate a table:
INSERT INTO [CHSQL01\ABOUTTIME].[abtt].[dbo].[EquipmentClock] ([equipmentId],[clockId])
	SELECT equipmentId, LEFT(b.Job,5) AS clockId
	FROM [CHSQL01\ABOUTTIME].[abtt].[dbo].[Equipment] a
	INNER JOIN VPTest.dbo.bEMEM b
	ON a.[equipmentCode] COLLATE DATABASE_DEFAULT = b.Equipment COLLATE DATABASE_DEFAULT
	WHERE b.Job IS NOT NULL

Open in new window

However, when I view the results, only 4 characters from the b.Job field are being imported.  What's perplexing is that when I run the select statement without the insert, it returns 5 characters as it should.  

Any ideas?

Edit: Please feel free to criticize my SQL as well.  I'm always trying to improve.
Comment
Watch Question


What datatype is the column [clockId] in the table [EquipmentClock] ??

Commented:
Not a space before that 4 charactes?
What are the column-specifications of b.job and clockId

Don't you have some trigger on the table [EquipmentClock] ??

Author

Commented:
Should have check that first.  Here's what I found:

clockId is an int
Job is a varchar

Most of the jobs are starting with a leading zero.

Author

Commented:
There aren't any triggers for the EquipmentClock table.

....
Most of the jobs are starting with a leading zero
.....

Thats It
Because clockId is an int and Job is a varchar

Try this, you will see

SELECT CONVERT(INT, '01234')

Commented:
Conversion without doing it explicit.... get's people waist time and is not always as you want it
-> the leading zero won't show in the int when you query it

Commented:
For the record
get's people waist time
is not about this question specific. If the you take the  habit of explicit writing your conversions it's just a reminder when you review your sql later or a collegue does. A reminder that this is something that can be a source of a problem.

Author

Commented:
Thanks for the advice, it's appreciated.

It looks like the solution to this problem, has led me to another now.  Namely, on the select statement, I need to have the second column (LEFT(b.job, 5)) now reference another table in order to ensure that it's pulling the right information.

In other words, I can't simply insert records from the job column even though they had the similar naming conventions.  I now need to make sure that a jobId now matches the id of the clock in another table, and have that return the clockId.

What would be the best way to do this?

Author

Commented:
To clarify, I would need to have the the number (b.job) reference another table, clocks, where it would look for a match in the crewName field.  From there it would return the proper clockId.

Would this be another inner join?
Commented:
Yes it will be another join, an inner join (must be present) seems to be right for the result you want but it also have to fit the reality of your db