| Author |
 Topic  |
|
|
allan8964
Posting Yak Master
196 Posts |
 Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/28/2012 : 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
196 Posts |
Posted - 11/28/2012 : 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. |
Edited by - allan8964 on 11/28/2012 14:26:44 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/28/2012 : 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
196 Posts |
Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/28/2012 : 15:07:35
|
I want to say that you can do something like shown below in red, but that may not be the best choiceinsert 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
196 Posts |
Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/28/2012 : 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. |
|
|
| |
Topic |
|
|
|
how to avoid duplication?
