SQl Database compare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQl Database compare

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-24 : 15:04:25

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 is
null) 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:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'newclients'.

Can anyone offer me an idea as to why they think that this may occur?

Thank you

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-24 : 15:11:36

Your syntax is incorrect. It should be INSERT and then SELECT, and without the parenthesis.

insert into msbtotal.dbo.newclients
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 is
null

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-24 : 15:27:10

First...are we talking about SQL Server?

Second...what is the goal? Do you want a complete copy? Then do a Dump and Restore..if not then don't you need check for old, new, updated rows?

Third...SELECT * and INSERT INTO with out a column list is just bad form...things can go BOOM

Are you the DBA? Do you have a DBA?

"My advice to you is to drink heavily..."

Anyone?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-24 : 17:34:07

Brett,

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.

Thanks

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-24 : 18:11:52

He is referring to using *. The best practice is to list out the column names explicitly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-24 : 22:27:28

quote:
Originally posted by dougancil
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.

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?

Are the 2 db's on the same server?

And Did Tara's advice help?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-25 : 10:52:36

Brett,

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 1
Insert 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.

Thanks

Doug

Kristen
Test

22859 Posts

Posted - 2010-08-25 : 11:03:22

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

list the columns you need, in the right order, instead of "SELECT *"

As it stands you will be getting all columns, for all joined tables, which is probably not what you want? If you just want columns from "tcms_members.dbo.memberdata" then this may do:


insert into msbtotal.dbo.newclients
SELECT S.* 
FROM tcms_members.dbo.memberdata AS S
    left outer join msbtotal.dbo.memberdata AS D
        on D.id = S.id 
where D.id is null

(S is my alias for Source, and D for Destination - hope I've got them the right way round!)

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-25 : 13:31:29

oye...

Do this

INSERT INTO myTable99(Col1, Col2, Col3, Col4, Col_ect)
SELECT Col1, Col2, Col3, Col4, Col_ect
FROM OtherTable99
WHERE Something = Reality

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-27 : 11:58:04

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

insert into msbtotal.dbo.newclients before my 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 is
null

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 13:15:11

Your query does not need to know what columns' data has changed. You just need to explicitly list out the column names to avoid the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-27 : 17:21:31

Tara,

When I tried listing out my column names (id,firstname,lastname,fullname,speciality,company,phonenumber,fax,birthday,streetaddr1,streetaddr2,city,state,zip,last_updated,date_added)
I get this error:

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'streetaddr2'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ID'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Firstname'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Lastname'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Fullname'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Speciality'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Company'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'phonenumber'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'fax'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'birthday'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'streetaddr1'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'streetaddr2'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'city'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'state'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'zip'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Last_Updated'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Date_Added'.

What is causing this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 17:30:21

Show us the CREATE TABLE statements plus your actual query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-30 : 10:30:33

Tara,

The table is already there. There is no "create table" statements. Here is my query though:


insert into msbtotal.dbo.newclients (id,firstname,lastname,fullname,speciality,company,phonenumber,fax,birthday,streetaddr1,streetaddr2,city,state,zip,last_updated,date_added)
SELECT id,firstname,lastname,fullname,speciality,company,phonenumber,fax,birthday,streetaddr1,streetaddr2,city,state,zip,last_updated,date_added 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 is
null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 12:52:45

You missed my point. We need to see the CREATE TABLE statements for the tables involved in your query so that we can fix your query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-30 : 16:19:53

Tara,

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 16:22:59

Yes these are needed. We aren't asking just for fun.

Please right click on the objects in Management Studio and script out the statements for us. Then post them here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-08-30 : 17:02:00

Tara,

Here is your create table statement

create table memberdata (ID char(10), firstname varchar (50), lastname varchar (50),
fullname varchar (50), speciality varchar (50), company varchar (50),phonenumber varchar(50),
fax varchar (50), streetaddr1 varchar (50), streetaddr2 varchar (50), city varchar (50),
state varchar (50), zip varchar (50), last_updated datetime , date_added datetime)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 17:15:32

No, we need the script that SSMS would generate.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 17:15:52

And we also need it for the newclients table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-01 : 10:18:04

Tara,

Since I'm not the DBA, how do I get the script the SSMS would generate for this?