Import without duplicates - 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

General SQL Server Forums

New to SQL Server Programming

Import without duplicates

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2010-07-15 : 09:19:59

Here's my situation. I think it's fairly simple to solve but I don't know where to start googling.

I have a SQL database with two tables.

Table 1 (wce_contact) is my main database table with 2 million records
Table 2 (wce_import) is my holding table with around 40k records that I want to import into my wce_contact table.

Both tables have identical fields, and in my website field (wce_import) there might already be some records in there that appear in wce_contact. Is there anyway at the point of merging two tables to stop the duplicates from being imported?

I hope my scenario makes sense to the Gurus on here.

JT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 09:30:11

Are you using SQL Server 2008? If so, use MERGE and WHEN NOT MATCHED.
Prior versions can make use of WHERE NOT EXISTS.

N 56°04'39.26"
E 12°55'05.63"

Topaz
Posting Yak Master

199 Posts

Posted - 2010-07-15 : 09:50:28

Hmm I'm using this as my insert query. (SQL 2005)

Not sure where I would inser WHERE NOT EXISTS

INSERT INTO [wce_site].[dbo].[wce_contact]
( [UNIQUEID]
,[expressemail]
,[website]
,[salutation]
,[Website1]
,[Website2]
,[Website3]
,[Blank])

SELECT
[UNIQUEID]
,[expressemail]
,[website]
,[salutation]
,[Website1]
,[Website2]
,[Website3]
,[Blank]
FROM [wce_import].[dbo].[wce_import]

JT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 09:58:25

[code]INSERT [wce_site].[dbo].[wce_contact]
(
[UNIQUEID]
,[expressemail]
,[website]
,[salutation]
,[Website1]
,[Website2]
,[Website3]
,[Blank]
)
SELECT src.[UNIQUEID]
,src.[expressemail]
,src.[website]
,src.[salutation]
,src.[Website1]
,src.[Website2]
,src.[Website3]
,src.[Blank]
FROM [wce_import].[dbo].[wce_import] AS src
WHERE NOT EXISTS (SELECT * FROM [wce_site].[dbo].[wce_contact] AS tgt WHERE tgt.UNIQUEID = src.UNIQUEID)[/code]

N 56°04'39.26"
E 12°55'05.63"

Topaz
Posting Yak Master

199 Posts

Posted - 2010-07-15 : 10:24:04

That did the trick. Have a virtual kiss from me!!! xx

JT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources