SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to avoid duplication?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

249 Posts

Posted - 11/28/2012 :  13:33:25  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/28/2012 :  13:41:49  Show Profile  Reply with Quote
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'  ...
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 11/28/2012 :  14:25:50  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/28/2012 :  14:30:10  Show Profile  Reply with Quote
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
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 11/28/2012 :  14:37:27  Show Profile  Reply with Quote
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'
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/28/2012 :  15:07:35  Show Profile  Reply with Quote
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.
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 11/28/2012 :  17:28:18  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/28/2012 :  17:52:26  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000