SQL INSERT INTO SELECT, combine varchar columns

Question

Say I have a table with columns firstName and lastName. Is it possible to do an INSERT INTO newTable SELECT firstName, lastName FROM oldTable, but instead of having two columns in the resulting table, concatenate the two columns?

Similarly, is it possible to concatenate a column with a static string?

Answer 1

Yes, you'd do

SELECT LTRIM(RTRIM(ISNULL(firstName, '') + ' ' + ISNULL(lastName, ''))) FROM oldTable

this concatinates the firstName and lastName columns, as well as the static string ' ' in between

EDIT: added LTRIM(RTRIM(...)) so if firstName or lastName is null, result won't have leading or trailing space as a result from the static ' ' string.