Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 453
eripey Avatar asked Apr 04 '13 19:04

eripey


People also ask

Can I use case statement with in insert statement?

We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well. In this article, we would explore the CASE statement and its various use cases.

Can we use case in insert statement in SQL Server?

in SQL. You can use the CASE expression to insert data into a SQL Server table. The INSERT statement with CASE will scan for the required values and if found, insert values from THEN expression.

Can we use insert and select together?

You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.

Can you insert data from another table into a SELECT statement?

We can still use the SQL INSERT INTO statement with a select statement. Let’s explore this in the next section. We can insert data from other SQL tables into a table with the following INSERT INTO SELECT statement. INSERT INTO table1 (col1, col2, col3, …)

What is the use of insert into select in SQL?

SQL INSERT INTO SELECT Statement 1 The SQL INSERT INTO SELECT Statement. The INSERT INTO SELECT statement copies data from one table and inserts it into another table. ... 2 Demo Database. In this tutorial we will use the well-known Northwind sample database. Obere Str. 57 49 Gilbert St. 3 SQL INSERT INTO SELECT Examples

How do you insert data into a table in SQL Server?

Introduction to SQL Server INSERT INTO SELECT statement. To insert data from other tables into a table, you use the following SQL Server INSERT INTO SELECT statement: INSERT [ TOP ( expression ) [ PERCENT ] ] INTO target_table (column_list) query. In this syntax, the statement inserts rows returned by the query into the target_table.

How to insert data from another table with supplying values?

Column name or number of supplied values does not match table definition. In this example, we’ll use the SQL INSERT INTO statement with supplying values directly in a statement. Suppose we want to insert data from another table. We can still use the SQL INSERT INTO statement with a select statement. Let’s explore this in the next section.


3 Answers

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
like image 140
Gordon Linoff Avatar answered Nov 11 '22 19:11

Gordon Linoff


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
like image 29
Taryn Avatar answered Nov 11 '22 20:11

Taryn


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

like image 42
Aleksandr Fedorenko Avatar answered Nov 11 '22 18:11

Aleksandr Fedorenko