Merge data from two tables into one table - no updates/only insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

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 new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

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.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you