Modifying data from one table and inserting it into another

Question

I have a staging table in my database where I download data from the FEC website. This table has the same structure as the one on the FEC web site and contains the following fields: CAND_ID , CAND_NAME, CAND_PTY_AFFILIATION, CAND_ELECTION_YR , CAND_OFFICE_ST , CAND_OFFICE , CAND_OFFICE_DISTRICT , CAND_ICI , CAND_STATUS , CAND_PCC , CAND_ST1 , CAND_ST2 , CAND_CITY, CAND_ST and CAND_ZIP .

I am attempting to make my database in normalized form. Therefore, I had to add some supporting tables. My Candidate table is structured like so: Cand_ID, Cand_Name, Cand_PartyID - REFERENCES tblParty(PartyID), Cand_Election_Yr , Cand_Office_St_ID - REFERENCES tblStates(StateID), Cand_Office_ID - REFERENCES tblCand_Office(OfficeID), CAND_OFFICE_DISTRICT, Cand_ICI - REFERENCES tblCand_ICI(ICI_ID), CAND_STATUS - REFERENCES tblCand_Status(StatusID), CAND_PCC, Cand_St1, Cand_St2, Cand_City, Cand_St - REFERENCES tblStates(StateID) and Cand_Zip. The fields in bold type are foreign keys that are referencing various supporting tables.

Here is my problem: I need to write a query that takes the staging table data and adds the appropriate information from the supporting tables and then places that data into my tblCand table. I can write an INNNER JOIN SQL procedure to get the data from the staging table and add the foreign keys. What I haven’t figured out is how to get the results from the query and add them into the tblCand table.

Here is the INNER JOIN query:
Use US_Oligarchs
Go

SELECT CAND_ID, CAND_NAME,CAND_ELECTION_YR,CAND_OFFICE_ST,CAND_ICI,CAND_STATUS,CAND_PCC , CAND_ST1,CAND_ST2,CAND_CITY,CAND_ST, CAND_ZIP FROM tblFEC_Raw_Data
INNER JOIN tblParty
ON tblFEC_Raw_Data.CAND_PTY_AFFILIATION = tblParty.Party
INNER JOIN tblStates AS ST1
ON tblFEC_Raw_Data.CAND_OFFICE_ST = ST1.StateAbbrv
INNER JOIN tblStates AS ST2
ON tblFEC_Raw_Data.CAND_ST = ST2.StateAbbrv
INNER JOIN tblCand_Office
ON tblFEC_Raw_Data.CAND_OFFICE = tblCand_Office.Office_Abbrv;

I have Murach’s SQL Server 2016 for developers Training & Reference book, but I haven’t found any reference on how to do what I described, and I don’t know how to ask this specific question on the Internet.

Can anyone offer and guidance?

Thank you,
MRM256

Answer 1

You didn't give us the definition of tblCard. Most likely the cause of this is you have additional columns in tblCard. The fix for this is to give the names of the columns you want to insert values in (which is always good practice). So your command would look something like

INSERT INTO tblCand(CAND_ID, CAND_NAME, CAND_ELECTION_YR, CAND_OFFICE_ST,  
CAND_ICI, CAND_STATUS, CAND_PCC,  
CAND_ST1, CAND_ST2, CAND_CITY, CAND_ST, CAND_ZIP)  
SELECT CAND_ID, CAND_NAME, CAND_ELECTION_YR, CAND_OFFICE_ST,  
CAND_ICI, CAND_STATUS, CAND_PCC,  
CAND_ST1, CAND_ST2, CAND_CITY, CAND_ST, CAND_ZIP  

The extra unlisted columns will get default values,

Tom

Answer 2

Hi @Mark McCumber

What I haven’t figured out is how to get the results from the query and add them into the tblCand table.

If your query get the correct result, then just use INSERT INTO tblCand SELECT ... FROM ... INNER JOIN ... INNER JOIN ... .

If you don't have 'tblCand' table in your database and you don't want to create it manually, then you can use SELECT Column_list INTO tblCand FROM ... INNER JOIN ... INNER JOIN ...
Refer to this doc for more details: SELECT - INTO Clause (Transact-SQL)

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.