how to avoid duplication? - 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

General SQL Server Forums

New to SQL Server Programming

how to avoid duplication?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-28 : 13:33:25

Hi there,

I need insert operations as follows:
(1)
insert table1(code, model, type, ...)
select code, model, 'A', ..
From table2
Where code = 'ABC' and model = 'VCT' ...

(2)
insert table1(code, model, type, ...)
select code, model, 'B', ..
From table2
Where code = 'ABC' ...

See, the ONLY difference is (2) has no model = 'VCT' in where clause. I use type of 'A' and 'B' to distinguish them later when I use them. But this way it creates lot duplications makign table huge. Is there any way to avoid the duplication before or during the insert operations? Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-28 : 13:41:49

You could change your insert to consider both cases like this:

insert table1(code, model, type, ...)
select code, model, CASE WHEN model = 'VCT' THEN 'A' ELSE 'B' END, ..
From table2
Where code = 'ABC'  ...

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-28 : 14:25:50

Thanks sunitabeck, it works well.
What if the situcation is like this:
(1)
insert table1(code, model, type, ...)
select code, model, 'A', ..
From table2
inner join table3 on .....
Where code = 'ABC' ...

(2)
insert table1(code, model, type, ...)
select code, model, 'B', ..
From table2
inner join table3 on .....
inner join table4 on ...
Where code = 'ABC' ...

Now the difference is on joined tables, and where cluase have same condistions?
Thanks again.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-28 : 14:30:10

What is the purpose of the join? Are you picking any additional columns from the new table3 in the second query? You could conceivably do something similar to the previous case, but it is hard to say without knowing the reason for the additional table join

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-28 : 14:37:27

Purpose is to separate the rows with different conditions. All the columns picked in joins tables are the same. Difference is (1) needs join table3 only while (2) needs join table3 and table4. Later when I use them I use

select ... from table1 where type = 'A' or
select ... from table1 where type = 'B'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-28 : 15:07:35

I want to say that you can do something like shown below in red, but that may not be the best choice

insert table1(code, model, type, ...)
select code, model,
	CASE WHEN table4.JoinColumn IS NULL THEN 'A' ELSE 'B' END,...
FROM
table2
inner join table3 on .....
LEFT join table4 on ...
Where code = 'ABC' ...

The reason I think it not the best approach is because, for example, if there are two rows in table4 for a given row in the table2 joined to table3, it will insert two rows in table1. If that is what you want, then it's ok. But if not, we will need to use different logic.

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-28 : 17:28:18

thanks for your efforts.
I tried that way, it returns all (2) rows but a few of (1) ...
I am thinking about maybe I need do something after insertion.
Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-28 : 17:52:26

If I had to venture a guess, I would say that is because there is a one to many mapping between t2 or t3 and t4. I still didn't quite get what you are joining table4 for - so my conjecture could very well be incorrect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources