Comparison of two different databases

[dougancil]

I have two different databases on the same server using SQL 2000. Every night database 1 is updated via an automated process. I currently have an query that will then move that data to a "static" table that I can manipulate however I need. What I'm trying to build is either a trigger or a SP that will allow me to check the differences between the newly uploaded data and the "static" data and then move whatever is new to a table called "newclients." I've looked at a lot of solutions on line but most seem to involve a long convoluted process to do this. Is there a relatively simple and easy way to write this. Currently, this is what I have:

create trigger newcustomers
on newclients
for insert as
insert into msbtotal.dbo.members
SELECT * FROM tcms_members.dbo.memberdata

Thanks

Doug

[NeoPa]

Doug,

Could you rewrite this do you think. It's hard to follow what you're trying to say. I suggest you use object names and then it will be clearer what you're referring to.

[dougancil]

Neo,

Ok I'll try to write this as simply as I can.

I have 2 databases. Database A is filled every night by an application that produces the data at midnight. So every night at midnight the data in database A is overwritten. Database B is a copy of database a before it's overwritten every night. What I'd like to do is before the data in database A is overwritten every night is to compare A and B, see whats different between the two and then whatever has been edited or added is then added to table "New." Does that make more sense?

[NeoPa]

Yes it does thank you. Not many names, but it is clearer so you get away with that ;) For now we can call the table in database A TableA and the table from database B TableB. Remembering of course that TableB holds the original data while TableA holds the updated data.

Essentially then I guess you want a table of Deltas, or effective changes, that have been made to the table during the process. Let me know if I have this wrong, but that would mean it should include all of :

1. Additions. Simple enough after-image of the data.
2. Amendments to existing records. This could be done as Before- & After-Images, or for less demanding situations simply an After-Image may do.
3. Deletions. These may not be required but they would be if you would need to be in a position to reproduce the changes.

Assuming access to both tables simultaneously then, you would create a query to append (into [New]) all records from TableA where the matching record from TableB (LEFT OUTER JOIN on all fields) doesn't exist. If a different flag is required for an After-Image of an amendment as opposed to that for an addition, then join on a unique index and compare the other data in the WHERE clause.

Pretty well the same technique can be used in reverse to find deletions and Before-Images of amendments. Two relatively straightforward queries can do the whole job for you.

PS. It's not about simplicity of the explanation, but rather about the clarity. Your original post was relatively simple, but not clear.

[ck9663]

If you're talking with just one table, you might be able to get away with simple code that compares the two tables and spit out the difference into another table. If it's multiple tables try the more complex world of triggers. If it's almost the entire server-to-server comparison, try the more complicated REPLICATION.

Good Luck!!!

~~ CK

[dougancil]

Neo ...

This is what I've come up with for now. What this doesnt call into clarity is any kind of error checking or anything like that .. and I am eventually going to create a "trigger" off of it. Just not at this time:

SELECT *
FROM   tcms_members.dbo.memberdata left outer join
       msbtotal.dbo.members
  on   tcms_members.dbo.memberdata.id = msbtotal.dbo.members.id
where  msbtotal.dbo.members.id is null

[NeoPa]

Doug,

Your posted SQL will show items that have been added, but only those items. I would have thought you might require more, but as long as you understand that's all it will do, and you're happy that's actually all you want, then you should be fine.

If you want more, but are still unsure of exactly how to go about it then let us know and I'm sure we can fill in more details for you.

[dougancil]

neo,

Actually what I'd like to do is to add some sort of error checking. Right now, if there's no data in the query, SQL returns an error and I'd like the query to check for that.

[dougancil]

Also, Neo, I just thought of this too ... my ID field in my table is my primary key which of course can't be overwritten. Essentially what I'll need to have happen is that daily both my databases will be overwritten, but if there is any new data in database 1 (between the last data dump and today) that data is added to database 2 but also "parsed" and the new information dumped to the newclient table.

[NeoPa]

Doug, there seems to be a bit of a problem here. I'm trying to answer a question for you, but the question is moving the closer we get to it. It really is very important to express the whole question clearly in the first post. All relevant details should be included as they will effect the answer which is most appropriate. If you feed in details piece-meal then the thread becomes a mess and is little use to anyone else with a similar problem (as well as being quite frustrating for anyone who finds themselves answering various questions as they change after each solution).

Having a generated PK (Like AutoNumber) where the creation of the record would typically not generate the same data every time, would mean that my earlier suggested approach would not be appropriate for you. Otherwise it would be of course. I'm not clear from your recent comments whether or not this is the case, but I suspect it may be from reading between the lines.

If irreplaceable unique IDs are involved then the earlier suggested approach would need to be amended to using three separate queries instead of the two suggested earlier.

1. DELETE any items that are no longer extant.
2. UPDATE any items where the associated data has changed.
3. APPEND any new items.

The design of the table to take the new data would need to be somewhat different, as specific IDs would be needed to be stored, rather than new IDs generated automatically on APPEND (as would be the case with an AutoNumber ID).

PS. While I'm happy to give further help where possible, I cannot always guarantee to be able to help if you decide not to follow the guidelines I lay down. Sometimes I can, but other times I may either not have much experience in a solution of that sort, or it may simply be a poor approach that I'm not willing to help anyone with. This is related more to indicating, by my involvement, that it is a valid approach (for other readers of the thread) than it is about any sense of imposing my views on the OP. Ultimately, only you shouild decide how to approach your project.

[dougancil]

Neo,

Sorry for the confusion, but this project has been a moving target for me as well. The person who "tasked" me with this project uttered those infamous words to me .."It's simple all you have to do is ...." and of course after spending time with the concept, I realized that they were missing a lot of what I consider checks and balances and error checking, so I apologize for my last minute edition to the issue. Where I stand today is as follows:

I have taken off the primary key on the ID fields, for now. MY query needs to overwrite the data in database 2 anytime a new "data dump" is done in database 1 (in our case it's every day at midnight) and then whatever is new is also put into a table called NewClients.

Here is what I have so far:

(SELECT * FROM tcms_members.dbo.memberdata left outer join
msbtotal.dbo.members on tcms_members.dbo.memberdata.id = msbtotal.dbo.members.id where msbtotal.dbo.members.id is null)
insert into msbtotal.dbo.newclients

[NeoPa]

Doug,

If I'm honest I'm still struggling to understand what you're asking. I feel my earlier response (post #4) answers the fundamental question given your latest comment about ignoring PKs for now at least.

Your latest post includes some SQL, but doesn't really ask a question with it. Are you saying that isn't working (Error messages generally help in such a situation)? Or are you asking what else needs doing on top of this (indicating you're happy with your current SQL)?

I would expect your current SQL to look like the following, but I'm often surprised to find T-SQL has other ways of doing things than I'm aware of :

INSERT INTO msbtotal.dbo.newclients
SELECT      *
FROM        tcms_members.dbo.memberdata AS tMD
            LEFT OUTER JOIN
            msbtotal.dbo.members AS tM
  ON        tMD.ID = tM.ID
WHERE       tM.ID IS NULL