Solved

Compare two tables for common string

Posted on 2014-02-14
6
300 Views
Last Modified: 2016-02-10
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
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39861197
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
 

Author Comment

by:zolf
ID: 39861203
by: Surendra Ganti

thanks for your comments. can you please explain what does the first line say ;WITH C AS
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39861231
that is called as common table expression (CTE)... this is newly introduced in SQL Server 2005
0
 

Author Comment

by:zolf
ID: 39862257
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39864087
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question