CODEWITHSUNDEEP
sqlsql-server
Jcmoney1010
Jcmoney1010

Reputation: 922

Preventing Duplicate Values in a SQL table

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?

Upvotes: 1

Views: 235

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Here is another way.

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

Upvotes: 0

M.Ali
M.Ali

Reputation: 69584

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])

Upvotes: 3

Related Questions