SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQl Database compare
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/24/2010 :  15:04:25  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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

USA
36895 Posts

Posted - 08/24/2010 :  15:11:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/24/2010 :  15:27:10  Show Profile  Reply with Quote
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



Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/24/2010 :  17:34:07  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/24/2010 :  18:11:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/24/2010 :  22:27:28  Show Profile  Reply with Quote
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



Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/25/2010 :  10:52:36  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/25/2010 :  11:03:22  Show Profile  Reply with Quote
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!)

Edited by - Kristen on 08/25/2010 11:05:21
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/25/2010 :  13:31:29  Show Profile  Reply with Quote
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



Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/27/2010 :  11:58:04  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/27/2010 :  13:15:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/27/2010 :  17:21:31  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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?

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/27/2010 :  17:30:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/30/2010 :  10:30:33  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/30/2010 :  12:52:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/30/2010 :  16:19:53  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/30/2010 :  16:22:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 08/30/2010 :  17:02:00  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/30/2010 :  17:15:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 08/30/2010 :  17:15:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/01/2010 :  10:18:04  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Tara,

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

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000