sql insert into table with select case values

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] sql insert into table with select case values

Examples related to sql

Examples related to sql-server-2008