How to convert two nvarchar rows into one when one of the row is null

sorry if this is a duplicate to one of existing questions开发者_StackOverflow (it's so simple but I can't figure it out, I'm new).

I need to migrate some data from one table to another (different structures).

Table A have Firstname and LastName columns. Table B have Name column

I want to do

SELECT Firstname + ' ' + LastName As Name FROM TableA 

But the problem is that in table B, some rows have null value for firstname or lastname but not both (Lazy user).

When I import them into table B, the query fails because Name column is non-nullable in my new design and when I test the statement above, if firstname or lastname is null, the concated value is null.

From the reading that I've done, this is expected behavior but what can I do to get around this?

I want to save firstname or lastname if the other is null.

SELECT RTRIM(LTRIM(ISNULL(Firstname ,'') + ' ' + ISNULL(LastName,''))) AS Name 
FROM TableA 

This can be done with a single colaesce (return first non-null) & no need to mess about with spaces.

select 
  coalesce(firstname + ' ' + lastname, firstname, lastname)
from TableA

use coalesce or isnull

 select COALESCE(FirstNAme, '') + ' ' + COALESCE(LastName, '') as name from TableA

SELECT isnull (Firstname, '') + ' ' isnull (LastName, '') as Name 
FROM TableA