SQL sql插入到带有选择案例值的表中

sql insert into table with select case values

提问by eripey

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'

我在关键字“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

回答by Gordon Linoff

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

end完成case语句后需要逗号。并且，“as”在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

回答by Taryn

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

您在案例中有别名，它需要在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

回答by Aleksandr Fedorenko

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

您也可以使用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

Demo on SQLFiddle

SQLFiddle 上的演示

回答by Registered User

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.

如果 FName 和 LName 包含 NULL 值，那么您将需要特殊处理以避免不必要的前导、尾随和中间空格。此外，如果 Address1 包含 NULL 值，那么您需要进行特殊处理以防止在地址字符串的开头添加不必要的 ', '。

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

如果您使用的是 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);

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