Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get values from two similar tables with Join condition Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2014 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:24 AM
Points: 5, Visits: 57
Hi everybody, I have an issue with the join of two tables without PK. Both are in two different servers and it structure is similar to this:

Cembarcador| Cemisor | Fremision | Remision | Tranportista | Volnat | Vehiculo | Cfacturador | Dfacturar |
690 | 68750 | 2013-12-31 00:00:00.000 | 287143 | NULL | 31.5010 | PR0179 | 300 | 99999 |
690 | 68750 | 2013-12-31 00:00:00.000 | 287144 | NULL | 29.4990 | PP113 | 300 | 99999 |
620 | 65750 | 2013-12-31 00:00:00.000 | 683830 | NULL | 27.7150 | PVI0001 | 300 | 99999 |
620 | 65750 | 2013-12-31 00:00:00.000 | 683831 | NULL | 31.4930 | PVI0001 | 300 | 99999 |
649 | 66750 | 2013-12-31 00:00:00.000 | 252927 | NULL | 31.0010 | PR3863 | 300 | 99999 |

Cembarcador (int, not null)
Cemisor (int, not null)
Fremision (datetime, not null)
Remision (numeric(12,0), not null)
Tranportista (varchar(50), null)
Volnat (numeric(12,40), null)
Vehiculo (varchar(20), null)
Cfacturador (int, null)
Dfacturar (int, null)

My problem is that I need to synchronize them using SSIS in order to know if exist any changes in the rows on the Destination DB or if exist the row in the Destination DB (comparing it with a main DB). I've done some tests but when I run the package it generates an infinite loop in the "Left Outer Join" part. I'm limited, I can't change the structure of the tables or database)

I'll appreciate any kind of help or ideas to solve this. Thank you.
Post #1564392
Posted Wednesday, April 23, 2014 11:59 AM This worked for the OP Answer marked as solution


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
If you are planning to sync to database tables between two different databases, you must be thinking of incremental data loads. In your case as I see it, may be a combination of (Cembarcador , Cemisor , Remision) may be used as a key to check the data.. I am not sure though too...

Take a look into this.. http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1564408
Posted Wednesday, April 23, 2014 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:24 AM
Points: 5, Visits: 57
Thanks it works perfectly for my situation :D
Post #1564437
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse