Problem tranforming field for adding to database
Posted on 2008-06-13
I have the following piece of code:
INSERT INTO dbo.crmContactTypes
ContactRef, Contact_Type, Volunteer, Region, Owner
TOP (100) PERCENT InitialData.right('00000' + cast(Contact_Migration_Ref as varchar(5)),5), Contact_Type.CRM_Contact_Type_ID,
crmContactData.Volunteer_Flag, InitialData.[PSYBT Area], Owner.CRM_User_ID
FROM InitialData INNER JOIN
Contact_Type ON InitialData.Code = Contact_Type.Code INNER JOIN
Owner ON InitialData.Owner = Owner.Owner LEFT OUTER JOIN
crmContactData ON InitialData.Contact_Migration_Ref = crmContactData.ContactRef COLLATE Latin1_General_CI_AI
WHERE (InitialData.LstNm IS NOT NULL) AND (InitialData.Contact_Migration_Ref IS NOT NULL) AND
(Contact_Type.CRM_Contact_Type_ID <> 99) OR
(InitialData.Contact_Migration_Ref IS NOT NULL) AND (Contact_Type.CRM_Contact_Type_ID <> 99) AND (InitialData.Position IS NOT NULL)
but when I run it I receive the following error:
Msg 156, Level 15, State 1, Procedure BusinessContactsUpdate, Line 114
Incorrect syntax near the keyword 'right'.
Now I know where the error is located: InitialData.right('00000' + cast(Contact_Migration_Ref as varchar(5)),5) but the problem I have is that the data being selected here is being translated as an int even though it's nvarchar. The data in this field is something like 01234 or 00070 but the starting 0s are being removed.
I need to be able to ensure that the starting 0s are not being removed from the code and assumed I'd be able to put something like the above code into my statement. I've used code like this before to perform the transformation but for some reason it's not working this time around.
Any ideas appreciated.