Search code examples
sqlsql-serverinsert-into

Using Insert Into Select while converting values to match a third table

I don't know how to simply phrase my question, but here is the scenario. I have two tables with data as follows:

CertStatus Table:

CertStatus_KEY  CertStatus_ID
1               ACTIVE
2               EXPIRED

CertImport Table:

Status
ACTIVE
EXPIRED
EXPIRED
ACTIVE
EXPIRED
ACTIVE

What I need to do is take the CertImport.Status column, convert all of those statuses to the CertStatus_KEY that matches the CertStatus_ID, and then copy all of that info into a third table with two columns, so the data would end up as follows.

Certification Table:

Certification_KEY   CertStatus_KEY
1                   1
2                   2
3                   2
4                   1
5                   2
6                   1

I'm trying to use an Insert Into Select statement but I get an error that says the Subquery returned more than 1 value. Here's what I've got:

INSERT INTO Certification (CertStatus_KEY)
SELECT (
    SELECT CertStatus_KEY from CertStatus where CertStatus_ID in (
        SELECT Status from CertImport)
    )

Simplified, the goal is to convert the CertImport.Status to the CertStatus.CertStatus_KEY value that corresponds to the matching CertStatus.CertStatus_ID, and then insert that value into Certification.CertStatus_KEY.

Thanks.

Solution 
  • Insert into Certification (CertStatus_Key)
select cs.Certstatus_Key
from CertStatus cs
inner join CertImport ci on cs.CertStatus_Id = ci.Status;

    DBFiddle demo

    Note: In SQL server, it is not guaranteed in which order the results would return from the select unless an order by clause is specified (or at least there is a clustered index). For a small data set like this you would get the result just as you asked but for a larger one there is no guarantee. Actually, design seems to be bad from the start, CertImport should have an ID column?