469,097 Members | 1,583 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

How to copy and compare data?

347 100+
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:

Expand|Select|Wrap|Line Numbers
  1. insert into msbtotal.dbo.newclients
  2. SELECT tcms_members.dbo.memberdata2.* FROM tcms_members.dbo.memberdata2 left outer join 
  3. msbtotal.dbo.memberdata on tcms_members.dbo.memberdata2.id = 
  4. msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
  5. null
  6. GO
  7. SET QUOTED_IDENTIFIER OFF 
  8. GO
  9. SET ANSI_NULLS ON 
  10. GO
  11.  

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

As you can see, there is duplicate data, and I need only one copy of each "new" record in my database. Can someone please assist me on this.

Thank you

Doug
Nov 5 '10 #1
3 1971
ck9663
2,878 Expert 2GB
Check for existence of the record before you insert it. Could you give us the header/column names of your data and which columns do you use to identify duplicate?

~~ CK
Nov 5 '10 #2
dougancil
347 100+
msbtotal.dbo.memberdata.id is what I would need to check against for duplicates. They are not auto generated by the server and are just characters. I hope that's the information that you're asking for.
Nov 5 '10 #3
dougancil
347 100+
Ck,

I modified my query like this:

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

That should work.
Nov 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Riegnman | last post: by
2 posts views Thread by Nick Hodge \(MVP\) | last post: by
1 post views Thread by Patrick C | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.