Solved

INSERT INTO SELECT JOIN THING

Posted on 2016-11-10
2
26 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:
ScottPletcher 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
Select2 jquery help 9 45
C# SQL BULK INSERT CLASS 5 35
sql calculate averages 18 30
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now