Insert Record - SQL Server Forums

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

Transact-SQL (2012)

Insert Record

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 11:52:57

Hi,

Here's the "Customer" table I have

Cus_ID Cus_code Cus_State

1 X Y
2 y N
3 Z N

How do I insert a new record into the same Customer table with new Cus_code = 'P' when Cus_code is X or Y and Cus_State is Y for those respective customer codes. I don't want any duplicate records to be inserted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 12:12:00

do you mean this?


INSERT INTO Customer (Cus_Code,Cus_State)
SELECT 'P',
'y'
FROM Customer
WHERE  EXISTS (SELECT 1 FROM Customer WHERE Cus_Code IN ('x','y') )
AND NOT EXISTS (SELECT 1 FROM Customer WHERE Cus_Code ='P' AND Cus_State ='y')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 13:01:10

I also want to check for customer id like when cus_id = '1'. How do I incorporate that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 13:02:57

add it as a condition inside NOT EXISTS using AND

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 13:03:38

Actually the cus_id is not a unique key. There could be different rows with same customer id.

Cus_id cus_code cus_state

1 X Y
1 Y N
1 Z N

Now, How do I insert a new record into the same Customer table with new Cus_code = 'P' when Cus_code is X or Y and Cus_State is Y for those respective customer codes. I don't want any duplicate records to be inserted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 13:11:47

so in above case how many new records it will insert? can you show table output after insertion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 14:26:53

Basically I want to insert a new row for every cus_id which has cus_code = X or cus_code = Y or cus_code = both x and y and cus_state = y for either one of them.

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-06 : 14:34:32

Here's the actual table customer

sub_id list_id cust_id ss_id sub_codesub_state
4363835 1 3275330 0 B2B N
4363836 1 3275330 0 CC Y
4363837 1 3275330 0 CU N
4363838 1 3275330 0 FND Y
4363839 1 3275330 0 MA N
4363840 1 3275330 0 MB Y
4363841 1 3275330 0 MNE N
4363842 1 3275330 0 MP N
4363843 1 3275330 0 MR N
4363844 1 3275330 0 MTL N
4363845 1 3275330 0 PD Y
4363846 1 3275330 0 PDD Y
4363847 1 3275330 0 SO Y
4363848 1 3275330 0 TV Y
4363849 1 3275330 0 WEB Y
4363850 1 3275330 0 YOC Y

I want to insert an additional record with sub_code = 'AWD' sub_state = 'y' for each customer who has sub_code = 'PDD' or 'TV' and sub_state = "Y". How do i do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 23:20:13

[code]
INSERT Customer (list_id,cust_id,ss_id,sub_code,sub_state)
SELECT list_id,cust_id,ss_id,'AWD','y'
FROM Customer
GROUP BY list_id,cust_id,ss_id
HAVING SUM(CASE WHEN sub_code IN ('PDD','TV') AND sub_State='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN sub_code = 'AWD' AND sub_state = 'y' THEN 1 ELSE 0 END)=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-07 : 10:13:38

This results in inserting two duplicate records with AWD when both PDD and TV cus_state is Y

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 11:00:07

nope..it will insert only a single record so far as list_id,cust_id,ss_id values are same from them as per your sample data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-07 : 14:47:38

The thing is I have multiple customers with different sub_codes for the same customer_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 14:58:44

so what. i'm not grouping by sub_code so you wont get any duplicates as per your earlier posted sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-07 : 15:39:35

This is what I get when I run the following query.

INSERT AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS (List_ID, Master_Customer_id, SS_ID, Subscription_code, Subscription_state, Add_Date)
SELECT List_ID, Master_Customer_id, SS_ID,'AWD','y', '2013-03-06 11:40:20.400'
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS Where Master_Customer_id = '03275330'
GROUP BY List_ID, Master_Customer_id, SS_ID, Add_Date
HAVING SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Subscription_code = 'AWD' AND Subscription_state = 'y' THEN 1 ELSE 0 END)=0

i don't want two records with the same subscription code AWD for the same customer id

sub_id list_id mastcustid ssid subcode substate adddate
4363835 1 3275330 0 B2B N 40:20.4
4363836 1 3275330 0 CC Y 40:20.4
4363837 1 3275330 0 CU N 40:20.4
4363838 1 3275330 0 FND Y 40:20.4
4363839 1 3275330 0 MA N 40:20.4
4363840 1 3275330 0 MB Y 40:20.4
4363841 1 3275330 0 MNE N 40:20.4
4363842 1 3275330 0 MP N 40:20.4
4363843 1 3275330 0 MR N 40:20.4
4363844 1 3275330 0 MTL N 40:20.4
4363845 1 3275330 0 PD Y 40:20.4
4363846 1 3275330 0 PDD Y 40:20.4
4363847 1 3275330 0 SO Y 40:20.4
4363848 1 3275330 0 TV Y 40:20.4
4363849 1 3275330 0 WEB Y 40:20.4
4363850 1 3275330 0 YOC Y 40:20.4
8304011 1 3275330 0 AWD y 40:20.4
8304012 1 3275330 0 AWD y 40:20.4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 00:17:33

quote:
Originally posted by maddyslayer

This is what I get when I run the following query.

INSERT AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS (List_ID, Master_Customer_id, SS_ID, Subscription_code, Subscription_state, Add_Date)
SELECT List_ID, Master_Customer_id, SS_ID,'AWD','y', '2013-03-06 11:40:20.400'
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS Where Master_Customer_id = '03275330'
GROUP BY List_ID, Master_Customer_id, SS_ID~~, Add_Date~~
HAVING SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Subscription_code = 'AWD' AND Subscription_state = 'y' THEN 1 ELSE 0 END)=0

i don't want two records with the same subscription code AWD for the same customer id

sub_id list_id mastcustid ssid subcode substate adddate
4363835 1 3275330 0 B2B N 40:20.4
4363836 1 3275330 0 CC Y 40:20.4
4363837 1 3275330 0 CU N 40:20.4
4363838 1 3275330 0 FND Y 40:20.4
4363839 1 3275330 0 MA N 40:20.4
4363840 1 3275330 0 MB Y 40:20.4
4363841 1 3275330 0 MNE N 40:20.4
4363842 1 3275330 0 MP N 40:20.4
4363843 1 3275330 0 MR N 40:20.4
4363844 1 3275330 0 MTL N 40:20.4
4363845 1 3275330 0 PD Y 40:20.4
4363846 1 3275330 0 PDD Y 40:20.4
4363847 1 3275330 0 SO Y 40:20.4
4363848 1 3275330 0 TV Y 40:20.4
4363849 1 3275330 0 WEB Y 40:20.4
4363850 1 3275330 0 YOC Y 40:20.4
8304011 1 3275330 0 AWD y 40:20.4
8304012 1 3275330 0 AWD y 40:20.4

this was not i suggested

modify like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-08 : 12:05:37

Thanks dude, It worked.

What does the following mean?

HAVING SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Subscription_code = 'AWD' AND Subscription_state = 'y' THEN 1 ELSE 0 END)=0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 12:09:16

the first condition checks for presence of records in table with Subscription_code IN ('PDD','TV') AND Subscription_state='y' for the same List_ID, Master_Customer_id, SS_ID value combination.
the second condition make sure you add the record with 'AWD','y' only if its not already present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-08 : 12:11:32

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 13:07:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-03-11 : 10:57:34

Can you please let me know how do I get all the customers with subscription codes "pdd" and "tv" and subscription state = y for both those codes?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 12:28:28

do you mean only records of customers having subscription codes "pdd" and "tv" and subscription state = y like below


SELECT *
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS 
WHERE Subscription_code IN ('PDD','TV') AND Subscription_state='y'

or all records of customers who have records with subscription codes "pdd" and "tv" and subscription state = y
ie like


SELECT *
FROM
(
SELECT *,SUM(CASE WHEN Subscription_code IN ('PDD','TV') AND Subscription_state='y' THEN 1 ELSE 0 END) OVER (PARTITION BY Master_Customer_id) AS Cnt
FROM AMA_SUB_SUBSCRIBER_SUBSCRIPTIONS
)t
WHERE Cnt >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources