Home of the Muppet Imperial Moderator Corps

SQL question

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.
Permalink Send private email sharkfish 
July 24th, 2007 5:34pm
Nevermind.  I'm just going to query both tables for a certain date or greater and compare that.
Permalink Send private email sharkfish 
July 24th, 2007 5:45pm
redgate to the rescue :P
Permalink Send private email sharkfish 
July 24th, 2007 6:39pm
RedGate SQL Data Compare is what we use, and it serves the purpose admirably.
Permalink DF 
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).
Permalink LinuxOrBust 
July 25th, 2007 2:12am
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.
Permalink Send private email sharkfish 
July 25th, 2007 8:16am
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.
Permalink Send private email sharkfish 
July 25th, 2007 8:17am
kinda late now, but is this what you were looking for?

http://p2p.wrox.com/topic.asp?TOPIC_ID=45365
Permalink Kenny 
July 25th, 2007 12:07pm
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).
Permalink Send private email LinuxOrBust 
July 25th, 2007 12:51pm

This topic is archived. No further replies will be accepted.

Other topics: July, 2007 Other topics: July, 2007 Recent topics Recent topics