INSERT INTO FROM: Concatenating Forename and Surname when Forename can be blank, 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!

P.

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").
Liya has a nice solution. This is another that handles both the null case and the empty string last name case as well. 

INSERT INTO [dbo].Test
select LTRIM(RTRIM (COALESCE(firstname,'') + ' ' + COALESCE(lastname,'')))
    ,field1
    ,field2
    ,fieldn
FROM RAW.Test ;


EDIT: Modified to allow either field to be nullable or an empty string with trimming in full effect in response to the modified requirements of the OP.