asked on

LEFT 5 only populating 4 characters on INSERT

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.

jvejskrab

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

jogos

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

jvejskrab

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

LouisvilleGeek

ASKER

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.

LouisvilleGeek

ASKER

There aren't any triggers for the EquipmentClock table.

ASKER CERTIFIED SOLUTION

jvejskrab

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jvejskrab

Try this, you will see

SELECT CONVERT(INT, '01234')

jogos

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

jogos

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.

LouisvilleGeek

ASKER

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?

LouisvilleGeek

ASKER

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?

SOLUTION

jogos

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer