Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Compare two tables for common string

Hello there,

I want to do this in SSIS. My situation is as follows. I have Supplier table and a table which has the supplier/product name and other details about the products. Both the table have a common column called SupplierName. Now I want to compare these two columns and if they are same then insert the ID from the Supplier Table into this second table where all are in one table. I tried with SSIS see shot,but I get error
2-15-2014-11-00-18-AM.gif
0
zolf
Asked:
zolf
  • 2
  • 2
1 Solution
 
Surendra NathCommented:
let us say the two tables in the first case Table1 and Table2.
the second table that you are talking about is TableA.

The common column here is ColumnX.

There are many ways to solve this issue, what I would do is to add a SQL task in the control flow and execute the below sql statement which will simplify the things

;WITH C AS 
(
  SELECT T1.ID ID FROM Table1 T1, Table2 T2 WHERE T1.ColumnX = T2.ColumnX
)
INSERT INTO TableA SELECT ID FROM C

Open in new window

0
 
zolfAuthor Commented:
by: Surendra Ganti

thanks for your comments. can you please explain what does the first line say ;WITH C AS
0
 
Surendra NathCommented:
that is called as common table expression (CTE)... this is newly introduced in SQL Server 2005
0
 
zolfAuthor Commented:
thanks for your comments. any idea why I get that error in my SSIS.I want to know the reason as to what I am doing wrong
0
 
ValentinoVCommented:
any idea why I get that error in my SSIS

The error says: "Row yielded no match during lookup", that means that the Lookup component did not find a matching value for one of the records in the batch.  That's probably not what you want because you'll have both matching and non-matching values coming in, right?  Open the properties of the Lookup transform and have a look at the dropdown.  It contains other options, like "Ignore failure" and "Redirect rows to no match output".

...and if they are same then insert the ID...

Are you sure you want to insert the ID, don't you mean update?  For update you'll have to use the OLE DB Command (not destination).
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now