How can I insert data from one SQL table to another table using Insert query

Question

Here is my first table:

And this is my second table:

I'm trying to create an INSERT query so that any of the columns with a result of 'Y' in the first table will be inserted into the second table, but I'm having trouble with the case statement that inserts different values other than 'Y'.

So in the first table, if one record has 'Y' for slp_c, sw_c and other_c, then in the second table after inserting, Discipline1_c = 'SLP', Discipline2 = 'SW' and Discipline3_c = 'OTH'. How do I write the case statement so that the values insert into the second table correctly?

INSERT INTO [dbo].[tb_cdsa_eligibility] ([uniqueid_c], [Discipline1_c],[Discipline2_c], [Discipline3_c])
    SELECT
        [uniqueid_c],
        -- this one is going into Discipline1
        CASE 
           WHEN [slp_c] = 'Y' THEN 'SLP' 
           WHEN [ot_c] = 'Y' THEN 'OT' 
           WHEN [SpecEd_c] = 'Y' THEN 'SE'  
           WHEN [medical_c] = 'Y' THEN 'MED' 
           WHEN [pt_c] = 'Y' THEN 'PT' 
           WHEN [sw_c] = 'Y' THEN 'SW' 
           WHEN [psych_c] = 'Y' THEN 'PSY' 
           WHEN [other_c] = 'Y' THEN 'OTH' 
           ELSE NULL 
        END,
        -- this one is going into Discipline2
        CASE 
           WHEN [slp_c] = 'Y' THEN 'SLP' 
           WHEN [ot_c] = 'Y' THEN 'OT' 
           WHEN [SpecEd_c] = 'Y' THEN 'SE'  
           WHEN [medical_c] = 'Y' THEN 'MED' 
           WHEN [pt_c] = 'Y' THEN 'PT' 
           WHEN [sw_c] = 'Y' THEN 'SW' 
           WHEN [psych_c] = 'Y' THEN 'PSY' 
           WHEN [other_c] = 'Y' THEN 'OTH' 
           ELSE NULL 
        END, 
        -- this one is going into Discipline3
        CASE
           WHEN [slp_c] = 'Y' THEN 'SLP' 
           WHEN [ot_c] = 'Y' THEN 'OT' 
           WHEN [SpecEd_c] = 'Y' THEN 'SE'  
           WHEN [medical_c] = 'Y' THEN 'MED' 
           WHEN [pt_c] = 'Y' THEN 'PT' 
           WHEN [sw_c] = 'Y' THEN 'SW' 
           WHEN [psych_c] = 'Y' THEN 'PSY' 
           WHEN [other_c] = 'Y' THEN 'OTH' 
           ELSE NULL 
        END    
FROM
    [cd].[tb_cdsa_eligibility]

3) You shouldn't really store computed values like this anyway, they tend to end up inconsistent after a while. Either create a view, or have computed columns. — jarlh
– jarlh, Commented Jun 30, 2020 at 20:18
It puts the same value in all 3 disciplines. So if slp_c is Y then it puts SLP in Discipline1_c, Discipline2_c and Discipline3_c. — Dee
– Dee, Commented Jul 1, 2020 at 15:04

Antonín Lejsek · Accepted Answer · 2020-07-02 02:08:54Z

You need to convert columns into rows and back into columns. That seems a good case for PIVOT

SELECT
    uniqueid_c,
    [1] AS Discipline1_c, 
    [2] AS Discipline2_c,
    [3] AS Discipline3_c            
FROM @tb_cdsa_eligibility 
OUTER APPLY (

    SELECT [1], [2], [3] FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY pos) AS position, val FROM (
                      SELECT 1 pos, CASE WHEN slp_c = 'Y' THEN 'SLP' ELSE NULL END val
            UNION ALL SELECT 2 pos, CASE WHEN ot_c = 'Y' THEN 'OT' ELSE NULL END val 
            UNION ALL SELECT 3 pos, CASE WHEN SpecEd_c = 'Y' THEN 'SE' ELSE NULL END val
            UNION ALL SELECT 4 pos, CASE WHEN medical_c = 'Y' THEN 'MED' ELSE NULL END val
            UNION ALL SELECT 5 pos, CASE WHEN pt_c = 'Y' THEN 'PT' ELSE NULL END val
            UNION ALL SELECT 6 pos, CASE WHEN sw_c = 'Y' THEN 'SW' ELSE NULL END val
            UNION ALL SELECT 7 pos, CASE WHEN psych_c = 'Y' THEN 'PSY' ELSE NULL END val
            UNION ALL SELECT 8 pos, CASE WHEN other_c = 'Y' THEN 'OTH' ELSE NULL END val
        ) AS allDisciplines 
        WHERE val IS NOT NULL
    ) AS notNullDisciplines
    PIVOT (
        MAX(val) FOR position IN ([1], [2], [3])
    ) AS pivotTable

) AS pivotedDisciplines
ORDER BY uniqueid_c;

critical_error · Accepted Answer · 2020-07-02 01:10:45Z

Here's a trick that I use with XML. It's not necessarily pretty, but it works for me.

The following may be run within SSMS:

-- Eligibility table mock-up.
DECLARE @tb_cdsa_eligibility TABLE (
    uniqueid_c VARCHAR(50), slp_c VARCHAR(1), ot_c VARCHAR(1), SpecEd_c VARCHAR(1), medical_c VARCHAR(1), pt_c VARCHAR(1), sw_c VARCHAR(1), psych_c VARCHAR(1), other_c VARCHAR(1)
);

-- Eligibility data.
INSERT INTO @tb_cdsa_eligibility ( uniqueid_c, slp_c, ot_c, SpecEd_c, medical_c, pt_c, sw_c, psych_c, other_c ) 
VALUES 
    ( '01058134223008B00D', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
    ( '081760744000051001', 'Y', NULL, NULL, NULL, NULL, 'Y', NULL, 'Y' ),
    ( '110161606330071025', NULL, NULL, NULL, NULL, 'Y', NULL, NULL, 'Y' ),
    ( '12014113922009C007', NULL, NULL, NULL, NULL, NULL, 'Y', NULL, 'Y' );

-- Preview the eligibility data.
SELECT * FROM @tb_cdsa_eligibility ORDER BY uniqueid_c;

Preview returns

+--------------------+-------+------+----------+-----------+------+------+---------+---------+
|     uniqueid_c     | slp_c | ot_c | SpecEd_c | medical_c | pt_c | sw_c | psych_c | other_c |
+--------------------+-------+------+----------+-----------+------+------+---------+---------+
| 01058134223008B00D | NULL  | NULL | NULL     | NULL      | NULL | NULL | NULL    | NULL    |
| 081760744000051001 | Y     | NULL | NULL     | NULL      | NULL | Y    | NULL    | Y       |
| 110161606330071025 | NULL  | NULL | NULL     | NULL      | Y    | NULL | NULL    | Y       |
| 12014113922009C007 | NULL  | NULL | NULL     | NULL      | NULL | Y    | NULL    | Y       |
+--------------------+-------+------+----------+-----------+------+------+---------+---------+

SSMS Continued

-- Select the first three disciplines for insert...
SELECT
    uniqueid_c,
    Disciplines.List.value ( 'data(//values/val)[1]', 'VARCHAR(10)' ) AS Discipline1_c,
    Disciplines.List.value ( 'data(//values/val)[2]', 'VARCHAR(10)' ) AS Discipline2_c,
    Disciplines.List.value ( 'data(//values/val)[3]', 'VARCHAR(10)' ) AS Discipline3_c
FROM @tb_cdsa_eligibility 
OUTER APPLY (

    -- Create an XML value list that can be queried...
    SELECT CAST ( ( 
        SELECT '<values>'
            + CASE WHEN slp_c = 'Y' THEN '<val>SLP</val>' ELSE '' END
            + CASE WHEN ot_c = 'Y' THEN '<val>OT</val>' ELSE '' END
            + CASE WHEN SpecEd_c = 'Y' THEN '<val>SE</val>' ELSE '' END
            + CASE WHEN medical_c = 'Y' THEN '<val>MED</val>' ELSE '' END
            + CASE WHEN pt_c = 'Y' THEN '<val>PT</val>' ELSE '' END
            + CASE WHEN sw_c = 'Y' THEN '<val>SW</val>' ELSE '' END
            + CASE WHEN psych_c = 'Y' THEN '<val>PSY</val>' ELSE '' END
            + CASE WHEN other_c = 'Y' THEN '<val>OTH</val>' ELSE '' END
        + '</values>'
    ) AS XML ) AS List

) AS Disciplines
ORDER BY uniqueid_c;

Returns

+--------------------+---------------+---------------+---------------+
|     uniqueid_c     | Discipline1_c | Discipline2_c | Discipline3_c |
+--------------------+---------------+---------------+---------------+
| 01058134223008B00D | NULL          | NULL          | NULL          |
| 081760744000051001 | SLP           | SW            | OTH           |
| 110161606330071025 | PT            | OTH           | NULL          |
| 12014113922009C007 | SW            | OTH           | NULL          |
+--------------------+---------------+---------------+---------------+

You can INSERT the results of the top three disciplines into your table or return the results directly like a normal query.

Note the use of data(//values/val)[n]. The value n references a specific index in the list of disciplines. NULL is returned in the event that the n index does not exist. You can also increase the number of disciplines returned by adding another "Discipline[#]_c" column and incrementing the value of n.

You can prioritize disciplines by changing the CASE order inside the OUTER APPLY. I defaulted to your column order in my example.

Collectives™ on Stack Overflow

How can I insert data from one SQL table to another table using Insert query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related