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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert help

Author  Topic 

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-24 : 12:12:11
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

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_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
[/code]

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

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?
Go to Top of Page

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
Go to Top of Page

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_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
[/code]

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

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' ?
Go to Top of Page

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 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
   

- Advertisement -