Duplicate values inserted - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate values inserted

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-11-29 : 03:52:44

Hi,

I have 2 tables (structure below)one called AccountsDim_temp and the other called AccountsDim.
I need to insert from AccountsDim_temp into AccountsDim. However, in AccountsDim I have a primary key called AccountNumber and therefore can't insert duplicate values into that field. I need to not insert the duplicate values. I tried the query below that I was given some time ago in this forum. It seemed to work but now it doesn't. How can I prevent duplicate values from being entered into AccountsDim?

Thanks for any help offered :-)

Insert into [AHT_DW].[dbo].[AccountsDim]  
(AccountNumber,AccountGroup,AccountType,AccountSubType,
AccountName)
select DISTINCT ISNULL(t2.AccountNumber,0)AccountNumber,ISNULL(t2.AccountGroup,0)AccountGroup,ISNULL(t2.AccountType,0)AccountType,
ISNULL(t2.AccountSubType,0)AccountSubType,
ISNULL(t2.AccountName,0)AccountName 
from  
 
 [AHT_DW].[dbo].[AccountsDim_temp] t2
 left join [AHT_DW].[dbo].[AccountsDim]  on t2.AccountNumber = [AHT_DW].[dbo].[AccountsDim].AccountNumber
Where [AHT_DW].[dbo].[AccountsDim].AccountNumber is null

_______________
CREATE TABLE [dbo].[AccountsDim_temp](
[AccountGroup] [nchar](300) COLLATE Hebrew_CI_AS NULL,
[AccountType] [nchar](300) COLLATE Hebrew_CI_AS NULL,
[AccountSubType] [nchar](300) COLLATE Hebrew_CI_AS NULL,
[AccountNumber] [nchar](300) COLLATE Hebrew_CI_AS NULL,
[AccountName] [nchar](300) COLLATE Hebrew_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[AccountsDim](
[AccountGroup] [nchar](300) COLLATE Hebrew_CI_AS NOT NULL,
[AccountType] [nchar](300) COLLATE Hebrew_CI_AS NOT NULL,
[AccountSubType] [nchar](300) COLLATE Hebrew_CI_AS NOT NULL,
[AccountNumber] [nchar](300) COLLATE Hebrew_CI_AS NOT NULL,
[AccountName] [nchar](300) COLLATE Hebrew_CI_AS NOT NULL,
CONSTRAINT [PK_AccountsDim] PRIMARY KEY CLUSTERED
(
[AccountNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 04:13:53

This is due to you having an isnull on the accountnumber in the select

ISNULL(t2.AccountNumber,0)AccountNumber

If you have more than one NULL, then you will have duplicates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-29 : 04:14:01

The distinct clause on SELECT is actually looking for distinct combination of (AccountNumber,AccountGroup,AccountType,AccountSubType,
AccountName) values rather than distinct AccountNumber value. Try this to get records with non duplicate AccountNumber

Insert into [AHT_DW].[dbo].[AccountsDim]
(AccountNumber,AccountGroup,AccountType,AccountSubType,
AccountName)
select ISNULL(t2.AccountNumber,0)AccountNumber,ISNULL(MIN(t2.AccountGroup),0)AccountGroup,ISNULL(MIN(t2.AccountType),0)AccountType,
ISNULL(MIN(t2.AccountSubType),0)AccountSubType,
ISNULL(MIN(t2.AccountName),0)AccountName
from

[AHT_DW].[dbo].[AccountsDim_temp] t2
left join [AHT_DW].[dbo].[AccountsDim] on t2.AccountNumber = [AHT_DW].[dbo].[AccountsDim].AccountNumber
Where [AHT_DW].[dbo].[AccountsDim].AccountNumber is null
GROUP BY ISNULL(t2.AccountNumber,0)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-11-29 : 04:40:50

Thanks all. I took away the Null and it seems to work now. I will also try visakh16 solution.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources