question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked ZoeHui-MSFT answered

How to append values in SQL table using SSIS

Hi Team,

Have two tables like bellow.
TableA:
123939-image.png


TableB:
123995-image.png


I need output like bellow

123955-image.png



Please help on same to achieve this.

sql-server-integration-services
image.png (23.1 KiB)
image.png (23.2 KiB)
image.png (25.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@balanarasimhac-1495,

While asking a question, you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements.
(2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL.
(3) Desired output, based on the sample data in #1 above.
(4) Your SQL Server version (SELECT @@version;).

All within the question, no images.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @balanarasimhac-1495,

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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi YitzhakKhabinsky-0887 ,

Thanks for quick support.

Have one more small doubt.

it will work If two tables on same sever

How to achieve same if one table in cloud and another table in on premise

0 Votes 0 ·

@@balanarasimhac-1495,

I updated the answer.
Please check its UPDATE section.

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @balanarasimhac-1495,

I think @YitzhakKhabinsky-0887 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.