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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  12:12:11  Show Profile  Reply with Quote
I have two tables

Customer
s_id cust_id date mdate cus_code cus_state
1 200 2/1/2012 4/1/2011 p y
2 200 2/2/2012 4/2/2011 p y
3 200 2/3/2012 4/3/2011 p y
4 200 2/4/2012 4/4/2011 q n
5 300 2/5/2012 4/5/2011 r y
6 300 2/6/2012 4/6/2011 r y
7 300 2/7/2012 4/7/2011 s y
8 300 2/8/2012 4/8/2011 s n
9 300 2/9/2012 4/9/2011 t y
10 400 2/10/2012 4/10/2011 t y
11 400 2/11/2012 4/11/2011 u n
12 400 2/12/2012 4/12/2011 t y

State

Cust_id flag
200 y
300 n
400 y

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 with the missing cus_codes and set the cus_state = N?

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/24/2013 :  12:48:31  Show Profile  Reply with Quote

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_code
FROM State s
CROSS JOIN (SELECT DISTINCT cus_code FROM Customer) t
)m
LEFT JOIN Customer c
ON c.cus_code = m.cus_code
AND c.cust_id = m.cust_id
WHERE c.cust_id IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  12:57:14  Show Profile  Reply with Quote
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?
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  12:59:20  Show Profile  Reply with Quote
and also cus_code is not in state table.p,q,r,s,t,u,v are the cus_codes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/24/2013 :  13:06:08  Show Profile  Reply with Quote

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_code
FROM State s
CROSS JOIN (SELECT DISTINCT cus_code FROM Customer WHERE flag = 'N') t
)m
LEFT JOIN Customer c
ON c.cus_code = m.cus_code
AND c.cust_id = m.cust_id
WHERE c.cust_id IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  15:46:15  Show Profile  Reply with Quote
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' ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/25/2013 :  00:55:13  Show Profile  Reply with Quote
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 first

i'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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.11 seconds. Powered By: Snitz Forums 2000