SQL Server Merge two tables together using insert statement to match exact values into table 1

aij0ehis 于 2023-04-28 发布在其他

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

I have two tables and I am trying to see how best to merge them into the first table using insert.

Table 1 Fields:
| date | cat_1 | cat_2 | cat_2 |
| ------------ | ------------ | ------------ | ------------ |
| 2020-01-02 | 1 | 2 | 0 |
| 2020-01-03 | 2 | 0 | 3 |

Table 2 fields:

date	cat_type	cat_count
2020-01-04	2	1
2020-01-04	3	2

I want to get the table 2 fields into table 1. How can I best do this? I am trying the following

INSERT INTO table 1 (FILE_DATE, cat_1, cat_2,cat_3) 
                FROM
                (SELECT DATE,
                    CASE WHEN cat_type = 2 THEN 'INSERT CAT_COUNT INTO CAT_2)
                    END
                FROM TABLE_2)

sql-server

来源：https://stackoverflow.com/questions/76056337/merge-two-tables-together-using-insert-statement-to-match-exact-values-into-tabl

2条答案

按热度按时间

vngu2lb81#

Basically you need to pivot table2 and then insert.

Something like this... FIDDLE

INSERT INTO Table1 (File_date, cat_1, cat_2, cat_3)  
  SELECT File_date
        , SUM(CASE WHEN cat_type = 1 THEN cat_count ELSE NULL END) AS cat_1
        , SUM(CASE WHEN cat_type = 2 THEN cat_count ELSE NULL END) AS cat_2
        , SUM(CASE WHEN cat_type = 3 THEN cat_count ELSE NULL END) AS cat_3
     FROM Table2
 GROUP BY File_date
  
SELECT * FROM TABLE1

赞(0）回复(0）举报 2023-04-28

nfzehxib2#

You can use Pivot to solve the problem

you can easy change List cat_? in pivot

insert into T1
(tdate,cat_1,cat_2,cat_3)

SELECT 
        tdate
       ,[1] as cat_1
       ,[2] as cat_2
       ,[3] as cat_3  
FROM   
(
    SELECT 
        tdate, 
        cat_type,
        cat_count
    FROM 
        T2
) t 
PIVOT(
    sum(cat_count) 
    FOR cat_type IN (
        [1], 
        [2], 
        [3] )
) AS pivot_table;

You can create insert base data with the following statements:

drop table if exists  T1
CREATE TABLE T1 ( tdate DATE, cat_1 INT, cat_2 INT, cat_3 INT)
drop table if exists  T2
CREATE TABLE T2 (tdate DATE, cat_type INT, cat_count INT)
INSERT INTO T2 VALUES ('2020-01-04',      2,        1)
INSERT INTO T2 VALUES ('2020-01-04',      3,        2);
INSERT INTO T2 VALUES ('2020-01-05',      3,        2);

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server Merge two tables together using insert statement to match exact values into table 1

2条答案

相关问题

热门标签

最新问答