copying and comparing data - 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

Transact-SQL (2000)

copying and comparing data

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-05 : 11:26:35

I have a query that I run daily against a database that is dumped to me nightly. I want to be able to populate a new database called Newclients and avoid duplicates. This is the query that I'm using:

insert into msbtotal.dbo.newclients
SELECT tcms_members.dbo.memberdata2.* FROM tcms_members.dbo.memberdata2 left outer join
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata2.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Here is some of my sample data:

6008272 Doctor A Doctor A, MD CHP UT SOUTHWESTERN PSYCHIATRY RES (512) 333-3333 (512) 333-3333 Sep 25 1977 12:00AM 1212 MockingBird Lane Austin TX 78737 2010-10-01 09:52:38.000 2005-06-06 00:00:00.000 NULL
6008272 Doctor A Doctor A, MD CHP UT SOUTHWESTERN PSYCHIATRY RES (512) 333-3333 (512) 333-3333 Sep 25 1977 12:00AM 1212 MockingBird Lane Austin TX 78737 2010-10-01 09:52:38.000 2005-06-06 00:00:00.000 NULL
6008272 Doctor A Doctor A, MD CHP UT SOUTHWESTERN PSYCHIATRY RES (512) 333-3333 (512) 333-3333 Sep 25 1977 12:00AM 1212 MockingBird Lane Austin TX 78737 2010-10-01 09:52:38.000 2005-06-06 00:00:00.000 NULL
6008272 Doctor A Doctor A, MD CHP UT SOUTHWESTERN PSYCHIATRY RES (512) 333-3333 (512) 333-3333 Sep 25 1977 12:00AM 1212 MockingBird Lane Austin TX 78737 2010-10-01 09:52:38.000 2005-06-06 00:00:00.000 NULL
6009837 Some Person Some Person, MD, MPH P UT SOUTHWESTERN PSYCHIATRY RES (512) 555-5555 (512) 555-5555 Jun 26 1975 12:00AM 3501 Residence Ave. Austin TX 78731 2010-09-29 11:53:30.000 2006-06-09 00:00:00.000 2007-11-28 00:00:00.000
6009837 Some Person Some Person, MD, MPH P UT SOUTHWESTERN PSYCHIATRY RES (512) 555-5555 (512) 555-5555 Jun 26 1975 12:00AM 3501 Residence Ave. Austin TX 78731 2010-09-29 11:53:30.000 2006-06-09 00:00:00.000 2007-11-28 00:00:00.000

Can someone please assist me on this.
Thank you

Doug

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-06 : 09:33:35

You have not stated what's the problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-11-06 : 12:17:51

Use a distinct clause in the select statement.

PBUH

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-08 : 09:20:46

I am using a distinct clause, but when I use DTS every night, it still seems that duplicates are being populated in my table. Here is the query that I have:

insert into msbtotal.dbo.newclients
SELECT distinct tcms_members.dbo.memberdata2.* FROM tcms_members.dbo.memberdata2 left outer join
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata2.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Everynight, the database is overwritten and then the next day I find duplicates. Using the query I have should work, but it's not. Any idea as to why?

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 12:18:14

Can you post some sample data of both the tables so that it can help us understand it better?

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 13:55:52

The information that we have so far is the following and you should correct me if I misunderstood:

1.
There is a destination table named newclients.
newclients should only take data of members which are the new members since the last night i.e. the last run of the script.
newclients is dropped or truncated every time before new data gets inserted.

2.
There is a table called memberdata which holds all members except the new members since last night.

3.
There is a table called memberdata2 which holds old and new data and this table is dumped to you every night.

4.
needed:
You script is looking for member data that is in memberdata2 but not in memberdata and puts it into newclient.

5.
the problem:
duplicates
In which table are the duplicates?
Where is the step that makes sure that the new data is coming also into memberdata so the workflow can work the next night?

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-09 : 14:51:39

The table newclients has duplicates in it. There isn't a step that makes sure that the data that's coming into the memberdata was there the night before, because it's all being overwritten nightly. What I've done though is I'm kind of "cheating" on this.

I have the comparison done first between the two tables, then I select distinct * and place into another table, I then drop the originating table, and rename the other table. I know it's not very elegant but it will do what I need at this moment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources