I have a staging (RAW) table that contains one field for to hold a person's first name, and another for a person's last name. I want to insert those value on a production (DBO) table, where the person's forename and surname will be concatenated:
INSERT INTO [dbo].Test SELECT firstname + ' ' + lastname ,field1 ,field2 ,fieldn FROM RAW.Test
Problem however is that I just found out that the firstname field can be NULL (no blanks, but NULL) in the RAW table. Considering the code I have above, this would mean that the person's name would be " Smith" (note the trailing space) instead of "Smith".
What would be the best way to check for such issue and only add the trailing space between firstname and lastname if firstname contains a value?
Note that firstname would never be blank, since the RAW table has a NULLIF conditional that replaces all blanks with NULL.
Thanks for your help and support!
EDIT: Sorry for the lame edit but I made a mistake, the firsname field is the one that can be blank (which makes sense if you see the example I gave about " Smith").