August 23, 2012 at 7:34 pm
Hello,
I need to replicate cust number as below. I do not want to use curser for the below since data volume is very very big (more than 5 mil records).
I really appreciate help on the below. Thanks.
Data now:
UrnColumn 1Column 3 Cust_No
2HD0200000 200000
3DAS1242059215 NULL
4DP12 NULL
5DA1PDD-GS NULL
6MP12 NULL
7MA1PDD-GS NULL
8HD02500000 2500000
9DAS1700938148 NULL
10DP12 NULL
11DA1PDD-EV NULL
Result I want to acheieve:
UrnColumn 1Column 3 Cust_No
2HD0200000 200000
3DAS1242059215 200000
4DP12 200000
5DA1PDD-GS 200000
6MP12 200000
7MA1PDD-GS 200000
8HD02500000 2500000
9DAS1700938148 2500000
10DP12 2500000
11DA1PDD-EV 2500000
August 23, 2012 at 7:55 pm
Sorry if my example in previous post was confusing, please find below more simplified data that needs replication. thanks
UrnCust_No
2200000
3NULL
4NULL
5NULL
6NULL
7NULL
82500000
9NULL
10NULL
11NULL
UrnCust_No
2200000
3200000
4200000
5200000
6200000
7200000
82500000
9200000
10200000
11200000
August 23, 2012 at 9:21 pm
1. Replication has completly different meaning in SQL Server from what you mean.
2. Your 2 examples are not equivalent: in 1st it seems you want to take first not NULL value and update NULLs until you hit another not NULL. In second it just read first value and update all NULLs to that value. So, which is it?
--Vadim R.
August 23, 2012 at 9:25 pm
sorry for the confusion.
Please consider second example that I provided. I need acheive below:
If Cust_No contain NULLs, I need to update the NULL value with the value from the previous row for that column that contain a non-null value.
I hope it is clear now? thanks
August 23, 2012 at 10:16 pm
Try this:
CREATE TABLE #Cust(
Urn INT
,Cust_No VARCHAR(20)
);
INSERT INTO #Cust(Urn, Cust_No)
VALUES
(2, 200000),
(3, NULL),
(4, NULL),
(5, NULL),
(6, NULL),
(7, NULL),
(8, 2500000),
(9, NULL),
(10, NULL),
(11, 25468),
(12, NULL),
(13, NULL)
--SELECT * FROM #Cust
DECLARE @CustNo VARCHAR(20);
UPDATE #Cust SET
@CustNo = Cust_No =
CASE WHEN Cust_No IS NOT NULL THEN Cust_No
ELSE @CustNo
END;
SELECT * FROM #Cust;
DROP TABLE #Cust;
--Vadim R.
August 23, 2012 at 10:20 pm
rVadim (8/23/2012)
1. Replication has completly different meaning in SQL Server from what you mean.2. Your 2 examples are not equivalent: in 1st it seems you want to take first not NULL value and update NULLs until you hit another not NULL. In second it just read first value and update all NULLs to that value. So, which is it?
Even the second example does not support what you mentioned here.:-)
The last values should be 2500000.
Urn Cust_No
2 200000
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 2500000
9 NULL
10 NULL
11 NULL
Urn Cust_No
2 200000
3 200000
4 200000
5 200000
6 200000
7 200000
8 2500000
[font="Verdana"]9 200000
10 200000
11 200000[/font]
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 23, 2012 at 10:21 pm
OK, simplified update:
UPDATE #Cust SET
@CustNo = Cust_No = COALESCE(Cust_No, @CustNo);
--Vadim R.
August 23, 2012 at 10:26 pm
Thanks all. It works
August 23, 2012 at 10:34 pm
mohan.mariyappa (8/23/2012)
Thanks all. It works
Hi Mohan,
Apart from your specific requirement.
I was thinking about the scenario where first record itself is having NULL.
You can validate that also in your logic.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 24, 2012 at 12:24 am
I don't think the record will have null values.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply