Preventing Duplicate Values in a SQL table

Question

I'm having a hard time preventing duplicate values from inserting into a specific table in Microsoft SQL Server. I have a staging table that I import data into each month and from this table Insert all the phone numbers into a phone numbers table. The Phone Numbers table is a simple table with 1 column and that column is set as the primary key.

The first time I go to insert the data from the staging table to the phone table, everything is fine because I can use distinct in my insert query to make sure there are no duplicates that enter into into the Phone Numbers table. The issue arises when I want to add another months worth of data and I have to compare what's in the new months data to whats in the Phone Numbers table.

Here is where I'm currently at, but this query is returning a The multi-part identifier "tbl_PhoneNumber.MTN" could not be bound. error

INSERT INTO tbl_PhoneNumber( MTN )
SELECT distinct [dbo].[tbl_Raw_Data].[Wireless Number]
FROM [dbo].[tbl_Raw_Data]

Where  tbl_PhoneNumber.[MTN] <>[tbl_Raw_Data].[Wireless Number]

Hopefully I didn't make this sound more confusing then what it is. What is the best way to compare two tables and only insert unique values?

possible duplicate of SQL Server Duplicate Checking

Bulat
– Bulat

2015-08-10 16:15:48 +00:00
Commented Aug 10, 2015 at 16:15 — Bulat
– Bulat, Commented Aug 10, 2015 at 16:15

M.Ali · Accepted Answer · 2015-08-10 16:05:28Z

3

Just make sure you have a column called MTN in your table and it should work fine.

INSERT INTO tbl_PhoneNumber( MTN )
SELECT distinct [Wireless Number]
FROM [dbo].[tbl_Raw_Data]
Where NOT EXISTS(SELECT 1
                 FROM tbl_PhoneNumber
                 WHERE tbl_PhoneNumber.[MTN] = [tbl_Raw_Data].[Wireless Number])

answered Aug 10, 2015 at 16:05

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jcmoney1010 Over a year ago

This worked perfectly, and I will accept it as the answer as soon as it lets me. I do have a questions though. What is purpose of the 1 after select? I tried something similar to this earlier, but didn't include the 1.

M.Ali Over a year ago

actually it doesnt have to be 1 it could be anything even a SELECT NULL will work just fine, actually exist operator just looks for anything, if anything is returned from the sub-query where the where condition evaluates to true the whole expression is evaluated to true for that particular row in the outer query.

Dan Bracuk · Accepted Answer · 2015-08-10 17:34:47Z

0

Here is another way.

insert into tbl_PhoneNumber (mtn)
select distinct Wireless_Number 
from tbl_Raw_data
except
select mtn
from tbl_PhoneNumber

answered Aug 10, 2015 at 17:34

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

Preventing Duplicate Values in a SQL table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related