Click here to Skip to main content
15,032,024 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have created a stored to create a table called Cubes.BankAccounts so when I do a select on my stored procedure I get duplicate records.

What I have tried:

CREATE TABLE #TempBankA(
	[Bank] int not  NULL,
	[AccountName] [varchar](255) NULL,
	[BankGroup]  int not NULL,
	[MoneyType] [varchar](255) NULL,
	[BankAccountType] [varchar](255) NULL,
	[Currency] [varchar](255) NULL,
	[Status] [nvarchar](64) NULL,
	[Office] [varchar](255) NULL,
	[GLType] [varchar](255) NULL,
	[CashGLAccount] int not NULL,
	[ContraGLAccount] int not  NULL,
	[AccountNumber] [nvarchar](64) NULL
) 


INSERT INTO #TempBankA
           ([Bank] 
	,[AccountName] 
	,[BankGroup]  
	,[MoneyType] 
	,[BankAccountType] 
	,[Currency] 
	,[Status] 
	,[Office] 
	,[GLType] 
	,[CashGLAccount] 
	,[ContraGLAccount]
	,[AccountNumber]
	)

SELECT [Bank] 
	,[BankAcct].Name
	,[BankGroup]  
	,[TrustAcctMoneyList].Description
	,[BankAcctType] 
	,[Currency] 
	,[BankAcctStatusList].Description
	,[Office] 
     ,[GLType] 
	,[CashGLAcct] 
	,BankAcctTrust.ContraGLAcct
	,[AcctNum]
from [BOWDW_Staging].[Elite_Staging].[BankAcct]
--left outer JOIN [BOWDW_Staging].[Elite_Staging].BankAcctStatusList ON [BOWDW_Staging].[Elite_Staging].BankAcct.BankAcctStatusList = [BOWDW_Staging].[Elite_Staging].BankAcctStatusList.Code
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctTrust] on  BOWDW_Staging.[Elite_Staging].[BankAcctTrust]. RelatedBankAcct = BOWDW_Staging.[Elite_Staging].BankAcct.BankAcctIndex
right outer join BOWDW_Staging.[Elite_Staging].[TrustAcctMoneyList] on TrustAcctMoneyList =BOWDW_Staging.[Elite_Staging].[TrustAcctMoneyList].Code
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctStatusList] on [BankAcctStatusList].Code = BOWDW_Staging.[Elite_Staging].[BankAcct].[BankAcctStatusList]
--Inner join BOWDW_Staging.[Elite_Staging].[BankGroup] on [BankGroup].BankGroupIndex = [BankGroup]

Declare @Count AS INT
SELECT @Count = count (*) from #TempBankA

If @Count > 0
BEGIN 
truncate table [Cubes].[BankAccounts_Load]

INSERT INTO [Cubes].[BankAccounts_Load]
(
	 [Bank] 
	,[AccountName] 
	,[BankGroup]  
	,[MoneyType] 
	,[BankAccountType] 
	,[Currency] 
	,[Status] 
	,[Office] 
	,[GLType] 
	,[CashGLAccount] 
	,[ContraGLAccount]
	,[AccountNumber]
)

SELECT 
	[Bank] 
	,[AccountName] 
	,[BankGroup]  
	,[MoneyType] 
	,[BankAccountType] 
	,[Currency] 
	,[Status] 
	,[Office] 
	,[GLType] 
	,[CashGLAccount] 
	,[ContraGLAccount]
	,[AccountNumber]
from #TempBankA
End
Posted
Updated 28-Jul-21 4:15am

1 solution

It is incredibly difficult for us to help you with a query of this type without table schemas nor sample data, let alone expected results. Therefore, this will be a very generic answer.

Firstly, I normally advise people to find out which JOIN is causing the additional records. One technique for doing this is remove all of joins and just run the query against the base table. E.g.
SQL
SELECT COUNT(*)
FROM [BOWDW_Staging].[Elite_Staging].[BankAcct]
-- WHERE clause if required
;
That should give you the number of rows you are expecting overall. Then add in the JOIN tables one-by-one until the duplication starts. E.g. 2nd step here would be
SQL
SELECT COUNT(*) 
FROM [BOWDW_Staging].[Elite_Staging].[BankAcct]
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctTrust] on  BOWDW_Staging.[Elite_Staging].[BankAcctTrust]. RelatedBankAcct = BOWDW_Staging.[Elite_Staging].BankAcct.BankAcctIndex
Once you determine which join is causing the problem you will need to investigate why. We can't help you with that without sample data.

Couple of other points that might help. Why are you using right outer join in that way? Consider these simple tables ...
SQL
create table #left (matchid int, comment varchar(20));
create table #right (matchid int, comment varchar(20));

insert into #left (matchid, comment) values
(1, 'left only'),
(2, 'in both');

insert into #right (matchid, comment) values
(3, 'right only'),
(2, 'in both');
This code
SQL
select *
from #left l
right outer join #right r on l.matchid =r.matchid
returns
matchid	comment	matchid	comment
NULL	NULL	3	right only
2		in both	2	in both
but this
SQL
select *
from #right r
right outer join #left l on l.matchid =r.matchid
returns
matchid	comment	matchid	comment
NULL	NULL	1	left only
2		in both	2	in both
I suspect neither of those results is what you are really after, but you probably want one line per [BankAcct] with additional columns of information from [BankAcctTrust], [TrustAcctMoneyList] and [BankAcctStatusList], which would be more like
SQL
select * 
from #left l
inner join #right r on l.matchid =r.matchid
Again, without sample data and expected results this is about as much as I can help you with that issue.

A comment about the readability of your code - consider using table aliases[^] to make things a little less crowded e.g.
SQL
from [BOWDW_Staging].[Elite_Staging].[BankAcct] ba
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctTrust] bt 
	on  bt.RelatedBankAcct = ba.BankAcctIndex
right outer join BOWDW_Staging.[Elite_Staging].[TrustAcctMoneyList] tl 
	on ba.TrustAcctMoneyList = tl.Code
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctStatusList] bl 
	on bl.Code = ba.[BankAcctStatusList]
Or at the very least, be consistent in how you refer to tables in your joins and keep it terse e.g.
SQL
from [BOWDW_Staging].[Elite_Staging].[BankAcct]
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctTrust] 
	on  [BankAcctTrust].RelatedBankAcct = BankAcct.BankAcctIndex
right outer join BOWDW_Staging.[Elite_Staging].[TrustAcctMoneyList] 
	on TrustAcctMoneyList = [TrustAcctMoneyList].Code
right outer join BOWDW_Staging.[Elite_Staging].[BankAcctStatusList] 
	on [BankAcctStatusList].Code = [BankAcct].[BankAcctStatusList]
Finally, one way of ensuring no duplicate records would be to add the word DISTINCT [^] to your final query i.e.
SQL
SELECT DISTINCT 
	[Bank] 
	,[AccountName] 
	,[BankGroup]  
	,[MoneyType] 
	,[BankAccountType] 
	,[Currency] 
	,[Status] 
	,[Office] 
	,[GLType] 
	,[CashGLAccount] 
	,[ContraGLAccount]
	,[AccountNumber]
from #TempBankA
End
But my advice would be to fix the underlying problem. It will certainly be more performant to fix the problem rather than query loads of data just to throw it away at the end.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900