[sql] sql insert into table with select case values

For some reason I am having trouble with this statement

Insert into TblStuff
(FullName,Address,City,Zip)

Select
Case
When Middle is Null Then Fname + LName as FullName,
Else Fname +' ' + Middle + ' '+ Lname as FullName,
End
Case
When Address2 is Null Then Address1 as Address,
else Address1 +', ' + Address2 as  Address,
End
City as City,
Zip as Zip
from tblImport

I am getting the incorrect syntax near keyword 'as'

Edited to add to this question, let me know if I need to add new post or not.

I know the below is little different statement, but can you make a case statement something similar to below? Does the below statment even make sense?

Insert into TblStuff
    (NickName,FirstName,MiddleName,Suffix)

    Case when FirstName IS NULL then 
        NickName as Nickname,
        IsNULL(FirstName,'''') as FirstName,
        IsNULL(MiddelName,'''') as MiddleName,
        IsNULL(NameSuffix,'''') as Suffix,
    Else
        IsNull(NickName2,'''') as NickName,
        IsNULL(FirstName,'''') as FirstName,
        IsNULL(MiddelName,'''') as Middlename,
        Case when NameSuffix2 is NULL then
            IsNULL(NameSuffix,'''')as suffix,
        Else
            IsNULL(NameSuffix,'''') + '''', '''' + IsNULL(NameSuffix2,'''') as suffix,
        End
    End
From tblImport

Also you can use COALESCE instead of CASE expression. Because result of concatenating anything to NULL, even itself, is always NULL

INSERT TblStuff(FullName,Address,City,Zip)
SELECT COALESCE(Fname + ' ' + Middle + ' ' + Lname, Fname + LName) AS FullName,
       COALESCE(Address1 + ', ' + Address2, Address1) AS Address, City, Zip
FROM tblImport

Demo on SQLFiddle

You need commas after end finishing the case statement. And, the "as" goes after the case statement, not inside it:

Insert into TblStuff(FullName, Address, City, Zip)
    Select (Case When Middle is Null Then Fname + LName
                 Else Fname +' ' + Middle + ' '+ Lname
            End)  as FullName,
           (Case When Address2 is Null Then Address1
                 else Address1 +', ' + Address2
            End)  as  Address,
           City as City,
           Zip as Zip
    from tblImport

If FName and LName contain NULL values, then you will need special handling to avoid unnecessary extra preceeding, trailing, and middle spaces. Also, if Address1 contains NULL values, then you need to have special handling to prevent adding unnecessary ', ' at the beginning of your address string.

If you are using SQL Server 2012, then you can use CONCAT (NULLs are automatically treated as empty strings) and IIF:

INSERT INTO TblStuff (FullName, Address, City, Zip)
SELECT FullName = REPLACE(RTRIM(LTRIM(CONCAT(FName, ' ', Middle, ' ', LName))), '  ', ' ')
    , Address = CONCAT(Address1, IIF(Address2 IS NOT NULL, CONCAT(', ', Address2), ''))
    , City
    , Zip
FROM tblImport (NOLOCK);

Otherwise, this will work:

INSERT INTO TblStuff (FullName, Address, City, Zip)
SELECT FullName = REPLACE(RTRIM(LTRIM(ISNULL(FName, '') + ' ' + ISNULL(Middle, '') + ' ' + ISNULL(LName, ''))), '  ', ' ')
    , Address = ISNULL(Address1, '') + CASE
        WHEN Address2 IS NOT NULL THEN ', ' + Address2
        ELSE '' END
    , City
    , Zip
FROM tblImport (NOLOCK);

You have the alias inside of the case, it needs to be outside of the END:

Insert into TblStuff (FullName,Address,City,Zip)
Select
  Case
    When Middle is Null 
    Then Fname + LName
    Else Fname +' ' + Middle + ' '+ Lname
  End as FullName,
  Case
    When Address2 is Null Then Address1
    else Address1 +', ' + Address2 
  End as  Address,
  City as City,
  Zip as Zip
from tblImport