tsql - SQL Help: Find which columns caused the mismatch -

The questions below select rows from table_1 which are not present in the table based on values ​​of 4 columns. Due to one or more columns may be mismatched.

I want to tell the query in detail what column (column) shows the mismatched values ​​either in the column name or its value. I can do this in the cursor but if possible it likes to do it in a set-based operation.

  Selection i.agent, i.agency, i.customer, i.Company as table_1 where I do not exist (SELECT p.agent, p.agency, p.customer, P.company from table_2 as p WHERE i.Agent = p.Agent and i.agency = p.Agency and i.customer = p.customer and i.company = p.Company)  

Update: I think that requires more refinement than this, let's add that 4 out of 4 columns need to match.

You can simplify this problem if you need to do some columns < / Em>, or at least some expectations in which the columns should match. In other words, instead of looking at it as a non-match problem, redefine it as a partial match problem.

Assume that you expect to match agent and agency , but clients and Company can not be . It is not very difficult:

  SELECT i.agent, i.agency, i.customer, i.company, p.customer, p.company, case when i.customer = p customer then Match category as 'Y' Els 'N', case when i.company = p.Company then match '1' in the form of 'Y' or 'N' end, in Table 1 I INNER pg = i.agent and p. Jansy = i.agency  

If you want to check other partial matches, just reorder the columns. Instead of joining on agent and agency , join agent and customer or whatever.

If you only expect a few different types of partial matches, you can write a few different questions as above and give them the UNION (or UNION ALL if you do not pay attention to duplicate). In other words: Select

  from (column) table 1 i from inner to table 2 p, select p.agent = i.agent and p.agency = i.agency union (column) ) Join Table 2P if you join table 1 i INNER pagment = i.agent and p.customer = i.customer  

, now if you every conceivable mismatch , then it is going to get out of control quickly, so you may want to customize a more intuitive method, and lose partial matches Which can be called at least is a fixed number of columns (3). You can then limit the maximum number of dislikes compared to your columns:

 ; With Partial Mats / CTA (Selection i.agent as iagent, p.agent as iagent, p.agent as pagent, ... (etc.) to join Table 1 Table 2PPont = i.agent Union All Selection (...) Table 1 Inner Please select P.Gence = i.agency Union all in table 2 (...) Join Table 1 INNER PXPN = i.company ... and so on) I AgentMatch, case when dispute = iagency then ' Y 'other' n 'end as an AgencyMatch, ..., (in case when on page = iagent then 1 ELSE 0 end + case when pagency = iagency then 1 ELSE 0 END + ...) The match was Rijhol select partial Mats_sitii) to mount Mets_sitii where Mackount & gt; = 3  

Now, after saying all this, one thing I am wondering is Ering ...

These two data tables are not sequentially related, they Are you As, the third line in Table 2 always maps to the third line in Table 2, but does not match all the columns? It's a shot in the dark, but if this is really the case, then we can simplify it by the way otherwise, the last question here should be that whatever you want very < / Em> without any unaccountable disturbances.

Note that the performance is likely to be smelly for all these questions. Hopefully your data sets are not huge AFAIK is not really an easy way to optimize this kind of thing.