I need to compare two tables from different databases (SQL Server) and update one from the other because the data from one overlaps the other.
The only think I have to go on is a user id and a date on each row.
The overlap date seems to depend on the user. Some users have overlap data from say 7/5/07 and earlier.
What query to tell the earliest overlap date?
This seem easy but I'm having a total brain fart.
Nevermind. I'm just going to query both tables for a certain date or greater and compare that.
RedGate SQL Data Compare is what we use, and it serves the purpose admirably.
July 24th, 2007 7:50pm
I think you are looking for a 'left join on id', which gives the union of the two tables, for that column.
That's right, my next SW will be nothing but a GUI connected to a left-join. ;-P
Ironically enough I want to make GUI versions of some command-line procedures that the business people follow at work (my own agenda, of course).
It would have to be left join on user id and date. But that still wouldn't work on a table that has multiple entries per user id and date and no way to uniquely identify the rows...?
So a user entered 3 items on 7/5 in one system. Entered one in the new system. Went back and re-entered one more in the new system, dated it 7/5, but it was entered on say, 7/7.
So my left outer join on user id, date would leave me with the one entry. I copy that to the new system.
That's what I did with Red Gate. Somehow the synch matched the total number of rows.
I should add that using Red Gate was simpler because I wanted the primary id on the table to be the same as the old, etc. You have to turn off identity insert, blah blah blah.
It seems like you need more fields, like autonumber as a PK, and then another date field (like one for date entered and one for date on document (if necessary)).
Anyhow, two separate steps perhaps. A left join on ID, then a left join on date using the results from the first left join.
I'm thinkin' perhaps it throws you off that RedGate does it one step (so you don't see the steps that it took).