How to append values in SQL table using SSIS

Question

Hi Team,

Have two tables like bellow.
TableA:

TableB:

I need output like bellow

Please help on same to achieve this.

Answer 1

Hi @Bala Narasimha Challa ,

It is an SQL question. So you can use SSIS Execute SQL Task.

SQL

-- DDL and sample data population, start  
DECLARE @tbl1 TABLE (ID INT IDENTITY PRIMARY KEY, market VARCHAR(30), segment VARCHAR(30), quantity INT);  
INSERT INTO @tbl1 (market, segment, quantity) VALUES  
('Africa', 'Corporate', 1934),  
('Europe', 'Home Office', 4626),  
('Europe', 'Consumer', 14144);  
  
DECLARE @tbl2 TABLE (ID INT IDENTITY PRIMARY KEY, market VARCHAR(30), segment VARCHAR(30), quantity INT);  
INSERT INTO @tbl2 (market, segment, quantity) VALUES  
('Africa', 'Corporate', 1000),  
('Europe', 'Home Office', 5000),  
('Europe', 'Consumer', 15000);  
-- DDL and sample data population, end  
  
;WITH rs AS  
(  
 SELECT market, segment, quantity FROM @tbl1  
 UNION ALL  
 SELECT market, segment, quantity FROM @tbl2  
)  
SELECT market, segment, SUM(quantity) AS quantity  
FROM rs  
GROUP BY market, segment  
ORDER BY rs.market, rs.segment;  

Output

+--------+-------------+----------+  
| market |   segment   | quantity |  
+--------+-------------+----------+  
| Africa | Corporate   |     2934 |  
| Europe | Consumer    |    29144 |  
| Europe | Home Office |     9626 |  
+--------+-------------+----------+  

--- Update ---
If your tables are located on different servers, your SSIS Data flow needs the following:

1. OLEDB Source (tbl1), OLEDB Source (tbl2)
2. Union All Task
3. Aggregate transformation
4. OLEDB Destination

Answer 2

Hi @Bala Narasimha Challa ,

I think @Yitzhak Khabinsky has given a perfect and detailed answer to resolve the issue.

You could mark it as answer so other user with similar problem could see this easier. :)

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

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.
Hot issues October