sql SELECT query - which merges 2 identical tables?

So I have 2 tables. Employee and Draft_Employee. They are identical, except that properties in Draft_Employee allow nulls.

The reason for the 2 identical tables, is that the draft_Employee is a table used for an import procedure - it is a tempoary container. We don't want the data messed up with the production employees.

Now, when a employee has to be imported, the system has to check if the employee already exists in the database.

First it see's if it can find an employee in Employee table with the same EmpID. If found, it will look at employee in Draft_Employee and find the properties which are NULL or EMPTY. It will then take the value for the same field in Employee table and put it into the empty or NULL fields in draft开发者_Python百科_Employee

empID   name   something1   something2   |   empID    name    something1   something2
-----   ----   ----------   ----------   |   ----     ----    ----------   -----------
1       Casper  blahblah    blahblah2    |   2        Michael NULL         text2fs
2       Michael txttxt                   |

Right is Employee and left is Draft_Employee.

I want an sql query that produces

empID   name     something1   something2
-----   ----     ----------   ----------
2       Michael  txttxt       text2fs

The closest I have come, is with LEFT OUTER JOIN but it gives me data from both tables

EDIT: My query. I did not use it before, because the spelling is danish.

SELECT * FROM Kladde_Ressource
LEFT OUTER JOIN Ressource
ON Ressource.RessourceID = Kladde_Ressource.RessourceID
WHERE Kladde_Ressource.EAN = ''
OR Kladde_Ressource.navnLang = ''
OR Kladde_Ressource.navnKort = ''
etc...

I don't entirely grasp your requirement so I am basing my query on the data and required result you've posted.

As to get the results merged into one result set

• use COALESCE to get the first non-NULL value from both tables.
• use an INNER JOIN to remove the non-matching rows.

Statement

SELECT  e.empID
        , COALESCE(e.name, de.name) AS name
        , COALESCE(e.something1, de.something1) AS something1
        , COALESCE(e.something2, de.something2) AS something2
FROM    employee e
        INNER JOIN draft_employee de ON de.empID = e.empID

This should do the trick:

SELECT Employee.empId, 
       ISNULL(Employee.name, Draft_Employee.name) AS name, 
       ISNULL(Employee.something1, Draft_Employee.something1) AS something1, 
       ISNULL(Employee.something2, Draft_Employee.something2) AS something2
FROM   Employee LEFT OUTER JOIN
       Draft_Employee ON Employee.empID = Draft_Employee.EmpId

Use the coalesce function to get the first non-null value:

select
  e.empID,
  coalesce(d.name, e.name),
  coalesce(d.something1, e.something1),
  coalesce(d.something2, e.something2)
from
  Employee e
  inner join Draft_Employee d on d.empID = e.empID

How about using UNION and GROUP BY:

SELECT empID, name, MAX(something1) AS something1, MAX(something2) AS something2
FROM (
    SELECT empID, name, something1, something2 FROM Employee WHERE empID = 2
    UNION
    SELECT empID, name, something1, something2 FROM draft_Employee WHERE empID = 2
)
GROUP BY empID, name