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.
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.newclientsSELECT 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 isnullGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOHere 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 NULL6008272 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 NULL6008272 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 NULL6008272 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 NULL6009837 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.0006009837 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.000Can someone please assist me on this.Thank youDoug |
|
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.newclientsSELECT 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 isnullGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOEverynight, 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:duplicatesIn 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. |
|
|
|
|
|
|
|