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.
Author |
Topic |
maddyslayer
Yak Posting Veteran
57 Posts |
Posted - 2013-04-24 : 12:12:11
|
I have two tablesCustomers_id cust_id date mdate cus_code cus_state1 200 2/1/2012 4/1/2011 p y2 200 2/2/2012 4/2/2011 p y3 200 2/3/2012 4/3/2011 p y4 200 2/4/2012 4/4/2011 q n5 300 2/5/2012 4/5/2011 r y6 300 2/6/2012 4/6/2011 r y7 300 2/7/2012 4/7/2011 s y8 300 2/8/2012 4/8/2011 s n9 300 2/9/2012 4/9/2011 t y10 400 2/10/2012 4/10/2011 t y11 400 2/11/2012 4/11/2011 u n12 400 2/12/2012 4/12/2011 t yStateCust_id flag200 y300 n400 yIf p,q,r,s,t,u,v are the cus_codes ,How do i insert the records in Customer table for each cust_id with the missing cus_codes and set the cus_state = N? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 12:48:31
|
[code]INSERT Customer (cust_id, date, mdate, cus_code, cus_state)SELECT m.cust_id,<dateval>,<dateval>,m.cus_code,'N'FROM(SELECT cust_id,cus_codeFROM State sCROSS JOIN (SELECT DISTINCT cus_code FROM Customer) t)mLEFT JOIN Customer cON c.cus_code = m.cus_codeAND c.cust_id = m.cust_idWHERE c.cust_id IS NULL[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
maddyslayer
Yak Posting Veteran
57 Posts |
Posted - 2013-04-24 : 12:57:14
|
If p,q,r,s,t,u,v are the cus_codes ,How do i insert the records in Customer table for each cust_id whose flag= 'n' in state table with the missing cus_codes and set the cus_state = N? |
|
|
maddyslayer
Yak Posting Veteran
57 Posts |
Posted - 2013-04-24 : 12:59:20
|
and also cus_code is not in state table.p,q,r,s,t,u,v are the cus_codes |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 13:06:08
|
[code]INSERT Customer (cust_id, date, mdate, cus_code, cus_state)SELECT m.cust_id,<dateval>,<dateval>,m.cus_code,'N'FROM(SELECT cust_id,cus_codeFROM State sCROSS JOIN (SELECT DISTINCT cus_code FROM Customer WHERE flag = 'N') t)mLEFT JOIN Customer cON c.cus_code = m.cus_codeAND c.cust_id = m.cust_idWHERE c.cust_id IS NULL[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
maddyslayer
Yak Posting Veteran
57 Posts |
Posted - 2013-04-24 : 15:46:15
|
I don't think this is correct.For example for cust_id = 300 Is it going to insert four records with cus_codes p,q,u,v(since those are the ones that customer doesn't have) where cus_state ='N' ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-25 : 00:55:13
|
quote: Originally posted by maddyslayer I don't think this is correct.For example for cust_id = 300 Is it going to insert four records with cus_codes p,q,u,v(since those are the ones that customer doesn't have) where cus_state ='N' ?
Check the output firsti'm doing a cross join with cust_codes so it will insert as much records for customer based on number of custcodes missing------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|