Solved

INSERT INTO SELECT JOIN THING

Posted on 2016-11-10
2
40 Views
Last Modified: 2016-11-11
Hi there!

I'm working in MSSQL Server 2008 R2

I have a table "AllQuarters" with a list of distinct employee numbers [EMPLOYEE] and fields for quarterly wages for those employees like so:
---------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE | Q1_GROSS_WAGES | Q2_GROSS_WAGES | Q3_GROSS_WAGES | Q4_GROSS_WAGES |
---------------------------------------------------------------------------------------------------------------------------------------------

I also have 4 tables (Q1 - Q4) that have the employee number [EMPLOYEE] and the gross wages [GROSS_WAGES] for the employee for that quarter.

I'm trying to join the Q1 - Q4 tables to the AllQuarters table on [EMPLOYEE] and insert the [GROSS_WAGES] from the appropriate quarter into its respective [QX_GROSS_WAGES] field in AllQuarters.

Not all employees exist in all Q1 - Q4 tables.  

I've tried INSERT INTO SELECT with a JOIN but my results end up with a bunch of NULL values.  Not sure what I'm doing wrong.  Any help will be greatly appreciated.  

TIA!
0
Comment
Question by:ttist25
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41882531
INSERT INTO AllQuarters ( EMPLOYEE, Q1_GROSS_WAGES, Q2_GROSS_WAGES, Q3_GROSS_WAGES, Q4_GROSS_WAGES )
SELECT COALESCE(Q1.EMPLOYEE, Q2.EMPLOYEE, Q3.EMPLOYEE, Q4.EMPLOYEE) AS EMPLOYEE,
    Q1.GROSS_WAGES, Q2.GROSS_WAGES, Q3.GROSS_WAGES, Q4.GROSS_WAGES  
FROM Q1
FULL OUTER JOIN Q2 ON Q2.EMPLOYEE = Q1.EMPLOYEE
FULL OUTER JOIN Q3 ON Q3.EMPLOYEE IN (Q1.EMPLOYEE, Q2.EMPLOYEE)
FULL OUTER JOIN Q4 ON Q4.EMPLOYEE IN (Q1.EMPLOYEE, Q2.EMPLOYEE, Q3.EMPLOYEE)
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 41883856
Thanks again Scott!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question