none
SQL Server Integration Service - Merging data - RRS feed

  • Question

  • Hi all,,

     

    May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

     

    I have a flat file that I want to merge with table in SQL server 2005.

     

    1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

     

    Now here is my question.

    I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

     

    I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a column in Table A only if the same column does not exist in Table A (there is no primary key).. 

     

    Here is an example:

    Table A

    --------------

    1 test test1 test2 test3 test4 test5

    2 test test6 test7 test8 test9 test10

     

    Table X

    ------------

    1 test test1 test2 test99 test4 test5

    2 test test98 test97 test 96 test95 test94

    --------------------------------------------------------

    Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1.. 

    The new Table A should look like

     

    NEW Table A'

    -----------------

        

    test test1 test2 test3 test4 test5

    test test6 test7 test8 test9 test10

    test test98 test97 test 96 test95 test94

     

    ------------------------------------

    I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

     

    Thanks in advance.

    Wednesday, September 5, 2007 8:33 PM

Answers

  • Off the top of my head I believe you can do this with a UNION statement.

    UNION by default will merge values the way you want.

     

    Here is a simple example you can run to verify:

     

    create table TableA (f1 int, f2 int)

    go

    create table TableB (f1 int, f2 int)

    go

    insert into TableA values (1,1)

    insert into TableA values (2,2)

    insert into TableB values (1,1)

    insert into TableB values (2,3)

    insert into TableB values (3,3)

    go

    select * from TableA

    union

    select * from TableB

    go

    -- Create a view to replace above UNION statement

    create view TableAB as

    select * from TableA

    union

    select * from TableB

    go

    -- Copy data from view into new target table C

    select * into TableC from TableAB

    go

    -- Drop the view, we no longer need it.

    drop view TableAB

    go

    select * from TableC

    Friday, September 7, 2007 9:18 PM
  • Tuesday, September 11, 2007 6:47 PM

All replies

  • Off the top of my head I believe you can do this with a UNION statement.

    UNION by default will merge values the way you want.

     

    Here is a simple example you can run to verify:

     

    create table TableA (f1 int, f2 int)

    go

    create table TableB (f1 int, f2 int)

    go

    insert into TableA values (1,1)

    insert into TableA values (2,2)

    insert into TableB values (1,1)

    insert into TableB values (2,3)

    insert into TableB values (3,3)

    go

    select * from TableA

    union

    select * from TableB

    go

    -- Create a view to replace above UNION statement

    create view TableAB as

    select * from TableA

    union

    select * from TableB

    go

    -- Copy data from view into new target table C

    select * into TableC from TableAB

    go

    -- Drop the view, we no longer need it.

    drop view TableAB

    go

    select * from TableC

    Friday, September 7, 2007 9:18 PM
  • Thanks matt..

     

    that was helpful sql statement. Is there a way to do it in business Intelligence Project..?.. may be I will need to post this in SSIS forum?

     

    Thanks again

     

    Monday, September 10, 2007 1:22 PM
  • I am assuming it should be easy to execute a sql statement in a Business Intelligence Project, but I could be wrong.  All the layers of helpful .NET code might make it difficult to do this, but probably not.

     

    Yes, I would make sense to post to SSIS to get their opinion on the best way to do the above in SSIS.

    Monday, September 10, 2007 6:34 PM
  • Tuesday, September 11, 2007 6:47 PM