Stored procedure duplicate records

Question

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 27-Jul-21 23:52pm

Patience “T” Moleko

Updated 28-Jul-21 4:15am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-07-28T04:15:00

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.