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 "Newclient."

I have the following query:(SELECT * FROM tcms_members.dbo.memberdata left outer join msbtotal.dbo.memberdata on tcms_members.dbo.memberdata.id = msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id isnull) insert into msbtotal.dbo.newclients

and when I run this query WITHOUT the insert statement, the query runs fine. When I use the insert statement, I get the following error:

Yes we are talking about SQL. 2000 in fact. The goal of this is pretty simple, database A is automated and dumped to us every night, database B will be a copy of database A, BUT before it's copied, they are compared, new data will also be put into a table called newclients. We don't have a DBA since we're a small shop so I'm having to do this solo. If Select * and Insert into is bad form, tell me what the best form is. I'm all about correcting other peoples mistakes before they become mine.

quote:Originally posted by dougancilThe goal of this is pretty simple, database A is automated and dumped to us every night, database B will be a copy of database A, BUT before it's copied, they are compared, new data will also be put into a table called newclients.

OK..do you know how they are compared, and when they are, what is the end result? Is the put new data in to NewClients that end result?

What Tara said does work but it really doesnt give me back a more user friendly error when there's no records that have been appended. I get the following error back:

Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.

What I'd like to do, since this is going to be a stored procedure, is to have a bit more of a user friendly error message when this occurs so that anyone who looks at the server logs, can tell that the SP tried to update the data but no "new" data was added. I think that this would be the place to add it.

Just so that I understand ... if I want to compare two tables, and put new information into another table, which I will have no idea what that new data is, then I have to supply column names? See the issue here is that this data is not controlled by me, and I have no idea what data is being changed so my query has to take into consideration that when the comparison is done between the two tables, that sometimes one field may be changed, none of the fields may have changed or several of them may have changed. I won't know from day to day which has changed (if any.) Right now what I'm left with is that if I try to run

I receive an error stating that "Insert Error: Column name or number of supplied values does not match table definition." and I have done a side by side comparison between the tables and all fields, columns and datatypes are identical.

These tables were manually created so there is no CREATE TABLE statement. I can create one if one is really needed. The Insert statement in my last posting has all of the table names. All (except for and last_updated and date_added, which are datetime) are varchar, default length of 50.