How to copy and compare data?

[dougancil]

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 [phone] [phone] 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 [phone] [phone] 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 [phone] [phone] 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 [phone] [phone] 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 [phone] [phone] 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 [phone] [phone] 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

[ck9663]

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

[dougancil]

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.

[dougancil]

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.