sql server—如何使用insert查询将数据从一个sql表插入到另一个表

2mbi3lxu 于 2021-08-01 发布在 Java

关注(0)|答案(2)|浏览(441)

这是我的第一张table：

这是我的第二张table：

我想创造一个 INSERT 查询，以便第一个表中结果为“y”的任何列都将插入到第二个表中，但case语句插入除“y”以外的其他值时遇到了问题。
因此，在第一个表中，如果一条记录的slp\u c、sw\u c和其他\u c的值为“y”，那么在插入后的第二个表中，discipline1\u c='slp'，discipline2='sw'，discipline3\u c='oth'。如何编写case语句以使值正确插入到第二个表中？

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]

sql sql-server

来源：https://stackoverflow.com/questions/62665404/how-can-i-insert-data-from-one-sql-table-to-another-table-using-insert-query

2条答案

按热度按时间

oipij1gg1#

您需要将列转换为行，然后再转换回列。这似乎是一个很好的例子 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;

展开查看全部

赞(0）回复(0）举报 2021-08-01

omvjsjqw2#

下面是我在xml中使用的技巧。不一定很漂亮，但对我很管用。
在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;

预览返回

+--------------------+-------+------+----------+-----------+------+------+---------+---------+
|     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（续）

-- 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;

退货

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

可以将前三个规程的结果插入表中，也可以像普通查询一样直接返回结果。
注意使用 data(//values/val)[n] . 值n引用规程列表中的特定索引。如果n索引不存在，则返回null。您还可以通过添加另一个“discipline[#]c”列并增加n的值来增加返回的规程数。
您可以通过更改 CASE 内部秩序 OUTER APPLY . 在我的示例中，我默认为您的列顺序。

展开查看全部

赞(0）回复(0）举报 2021-08-01

我来回答

sql server—如何使用insert查询将数据从一个sql表插入到另一个表

2条答案

相关问题

热门标签

最新问答