SQL INSERT与选择情况下的值表(sql insert into table with sel

出于某种原因，我有这种说法麻烦

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

我越来越近关键字的不正确的语法“为”

编辑补充到这个问题，让我知道，如果我需要增加新的岗位或没有。

我知道下面的是一些不同说法，但你可以做类似下面的一个case语句的东西吗？ 请问下面statment甚至有意义吗？

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

Answer 1:

您需要后逗号end整理的case语句。 而且，“为”云后case发言，不是它里面：

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

Answer 2:

你的情况下内部的别名，它需要的外部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

Answer 3:

你也可以使用COALESCE代替CASE表达式。 由于串联什么NULL，甚至本身的结果，始终是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

演示上SQLFiddle

Answer 4:

如果FName参数和LName的包含空值，则需要进行特殊处理，以避免不必要的额外前述，拖影，中间空间。 另外，如果地址1包含NULL值，那么你需要有特殊的处理，以防止在您的地址字符串的开头增加不必要的“”。

如果您正在使用SQL Server 2012，那么你可以使用CONCAT（NULL是为空字符串自动处理）和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);

否则，这将工作：

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);

文章来源: sql insert into table with select case values