November 13, 2011 at 6:13 am
I have a prospects table and prospectsincampaigns table.
See the sample database here: http://www.wunderwedding.com/files/db_initiliazed.zip
I want to update the campaignX column based on the value of prospectsincampaigns.campaignid:
if prospectsincampaigns.campaignid=12 for prospectsincampaigns.prospectid=300, I want to update campaign12 column in prospects for prospects.id=300
if prospectsincampaigns.campaignid=14 for prospectsincampaigns.prospectid=345, I want to update campaign14 column in prospects for prospects.id=345
and so on.
I also want to update the appointmentdateX column based on the value of prospectsincampaigns.result, but only when the result=15:
if prospectsincampaigns.result=15 for prospectsincampaigns.prospectid=623 for prospectsincampaigns.campaignid=10, I want to update appointmentdate10 column in prospects for prospects.id=623
if prospectsincampaigns.result=15 for prospectsincampaigns.prospectid=1023 for prospectsincampaigns.campaignid=50, I want to update appointmentdate50 column in prospects for prospects.id=1023
And so on
The maximum number of campaigns is 70.
The prospects table has 110.000 records and prospectsincampaigns has 160.000 records.
Performance is critical in this case.
The script only has to run once, since after this the entire DB will be migrated.
Help is greatly appreciated!
November 13, 2011 at 6:43 am
I'm willing to give this a shot. But there's way too little data to get anything meaningfull performance wise.
I understand you need to sanitize the data (P.S. you might want to drop the procedures as well from the DB).
Normally I would try recreating a decent sample dataset but with a few 100 columns that's just too much work.
If you want you can update the compagny name & adress to NULL or a short string to keep the table size the same.
Making that update is rather easy, but making it run fast is a hell of another story.
On my end, I don't mind downloading a 1GB+ zipped file of a backup, so the more data you can keep in there, the better.
November 13, 2011 at 6:48 am
What's I'd do here is make a coy of prod.
Drop all non-needed tables, even the asp.net ones. I<d drop all functions & sps as well.
Sanitize the sensitive information.
Then take a backup and zip it. Post the size here if it's more than 1 GB and we'll go from there.
November 13, 2011 at 7:16 am
A few observations first:
petervdkerk (11/13/2011)
I have a prospects table and prospectsincampaigns table.See the sample database here: http://www.wunderwedding.com/files/db_initiliazed.zip
I went ahead and downloaded and restored the file, but I should point out that it's generally a better idea to post scripts to generate your tables and populate them with sample data; it's usually a lot faster for someone to run your scripts than to download and restore the .bak file.
I want to update the campaignX column based on the value of prospectsincampaigns.campaignid:
if prospectsincampaigns.campaignid=12 for prospectsincampaigns.prospectid=300, I want to update campaign12 column in prospects for prospects.id=300
if prospectsincampaigns.campaignid=14 for prospectsincampaigns.prospectid=345, I want to update campaign14 column in prospects for prospects.id=345
and so on.
What value are you wanting to assign to campaignX? In my example further down I'm going put an 'X' in the column, you'll have to modify the script based on what your actual needs are.
The prospects table has 110.000 records and prospectsincampaigns has 160.000 records.
Performance is critical in this case.
The script only has to run once, since after this the entire DB will be migrated.
Out of curiosity, if the script only has to run once, why is performance critical? I'm a big believer in tuning queries to improve performance, but for a one-off data migration script...I would be more concerned with whether it works correctly than with trying to get it to run in one minute instead of five, for example. And along those lines, 110K-160K records really isn't a very large data set at all for SQL Server, so I really wouldn't be too concerned about finding the optimal solution to this problem, since almost any approach is going to be relatively quick and only a one-time expense. 🙂
So, on to a potential solution. 🙂 I think in this case, dynamic SQL is going to be your best bet, since you have to update different columns based on the values read from ProspectsInCampaigns. To get you started with the dynamic SQL approach, here's how you could generate all the UPDATE statements for your first requirement:
SELECT 'UPDATE dbo.Prospects SET campaign' +
CONVERT(VARCHAR(2), campaignid) +
' = ''X'' WHERE id = ' +
CONVERT(VARCHAR(7), prospectid ) +
';' AS UpdCmd
FROM ProspectsInCampaigns
Running that will spit out a bunch of SQL statements that look like this:
UPDATE dbo.Prospects SET campaign1 = 'X' WHERE id = 1359729;
UPDATE dbo.Prospects SET campaign1 = 'X' WHERE id = 1359729;
UPDATE dbo.Prospects SET campaign2 = 'X' WHERE id = 1359729;
UPDATE dbo.Prospects SET campaign4 = 'X' WHERE id = 1359730;
UPDATE dbo.Prospects SET campaign4 = 'X' WHERE id = 1359732;
UPDATE dbo.Prospects SET campaign1 = 'X' WHERE id = 1359732;
In order to execute them automatically, you could wrap that logic in a cursor or WHILE loop structure and use EXEC() to run each statement (yes, that's usually a terrible approach, but for a one-off I'm not sure it matters...) Or, you could just save the output to a file and execute it via SSMS or the osql utility.
Hopefully this will help get you started; let me know if you need further assistance.
EDIT: Removed the CASE statement option I referred to...that was a dumb idea... :hehe:
November 13, 2011 at 7:23 am
Ninja's_RGR'us (11/13/2011)
Making that update is rather easy, but making it run fast is a hell of another story.
...which is why I'm interested in understanding more about why performance is critical. Unless I'm really misunderstanding the OP (quite possible, haven't had my second cup of coffee yet!) this is a one-shot data migration job; if so, I would really have to question the value of trying to squeeze a lot of performance out of it (hence my suggestions above). But if there really is a need for high performance, it definitely becomes a trickier problem...
November 13, 2011 at 7:38 am
JonFox (11/13/2011)
Ninja's_RGR'us (11/13/2011)
Making that update is rather easy, but making it run fast is a hell of another story....which is why I'm interested in understanding more about why performance is critical. Unless I'm really misunderstanding the OP (quite possible, haven't had my second cup of coffee yet!) this is a one-shot data migration job; if so, I would really have to question the value of trying to squeeze a lot of performance out of it (hence my suggestions above). But if there really is a need for high performance, it definitely becomes a trickier problem...
This is the 2nd post about the subjet.
The original code is a hell of a lot longer than this. The first time they tried running it it hadn't completed after ±1.5 days (cursor with 30 statments inside). And by my best guess, it had data quality bugs in it.
They are migrating the data to another system. So this is a 1 time event. HOWEVER they need to be able to run multiple tests and since data quality here is of the utmost importance, there will be a lot of tests.
So while it doesn't need to be subsecond, it needs to be able to run in a few minutes and at the absolute most 1 hour.
The query for this is easy-ish. But I need the real data distribution to make it run fast. I can't just test back and forth, I need 100% accurate sample data (hence drop the contact info, making the rest of it untrackable in real life). We don't have 2 weeks to get this tuned over forums, it needs to be done yesterday, literally :hehe:.
November 13, 2011 at 7:39 am
P.S. 100K + prospect, millions of prospect campains.
That code never finished in a cursor and it probably won't this time either. (Yes I know you didn't know :hehe:).
November 13, 2011 at 7:41 am
Ninja's_RGR'us (11/13/2011)
P.S. 100K + prospect, millions of prospect campains.That code never finished in a cursor and it probably won't this time either. (Yes I know you didn't know :hehe:).
Oops, I didn't realize there was a larger context here, didn't see the other post. Sorry about that, didn't mean to muddy the waters!
November 13, 2011 at 7:47 am
Hah, turns out I had seen the other post, but didn't make the mental connection to this one. Yep, that's a lot more complex than I initially realized from reading this post.
* backs away slowly... *
November 13, 2011 at 7:47 am
JonFox (11/13/2011)
Ninja's_RGR'us (11/13/2011)
P.S. 100K + prospect, millions of prospect campains.That code never finished in a cursor and it probably won't this time either. (Yes I know you didn't know :hehe:).
Oops, I didn't realize there was a larger context here, didn't see the other post. Sorry about that, didn't mean to muddy the waters!
Don't even be sorry about doing your best to help someone.
I don't check post history of people before answering either.
I just happened to have participated in the other thread as well. :-D.
November 13, 2011 at 7:49 am
JonFox (11/13/2011)
Hah, turns out I had seen the other post, but didn't make the mental connection to this one. Yep, that's a lot more complex than I initially realized from reading this post.* backs away slowly... *
:w00t:
😀
Ya those kind of jobs usually take 10-40 hours, on the low end. Since performance doesn't need to be astonishing, we can cut out a lot of the tuning from the estimate. Just put enough to make it tolerable for the couple days left on the project.
November 13, 2011 at 8:16 am
Ok, I've placed a backup of the sanitized DB right here.
http://www.wunderwedding.com/files/sanitized.zip
Hopefully that is enough data to be able to create the correct queries?
November 13, 2011 at 8:38 am
yes that should be fine.
Tx.
November 13, 2011 at 5:06 pm
Here you go. I've wrapped the whole thing into a begin / rollback transaction.
Once you've validated that this works ok for you, change the rollback at the end to a commit and the data will be updated.
It's not super fast but I think that's good enough for the time being.
BEGIN TRAN
;WITH CTE(prospectid, CID1, CID2, CID3, CID4, CID5, CID6, CID7, CID8, CID9, CID10, CID11, CID12, CID13, CID14, CID15, CID16, CID17, CID18, CID19, CID20, CID21, CID22, CID23, CID24, CID25, CID26, CID27, CID28, CID29, CID30, CID31, CID32, CID33, CID34, CID35, CID36, CID37, CID38, CID39, CID40, CID41, CID42, CID43, CID44, CID45, CID46, CID47, CID48, CID49, CID50, CID51, CID52, CID53, CID54, CID55, CID56, CID57, CID58, CID59, CID60, CID61, CID62, CID63, CID64, CID65, CID66, CID67, CID68, CID69, CID70, AID1, AID2, AID3, AID4, AID5, AID6, AID7, AID8, AID9, AID10, AID11, AID12, AID13, AID14, AID15, AID16, AID17, AID18, AID19, AID20, AID21, AID22, AID23, AID24, AID25, AID26, AID27, AID28, AID29, AID30, AID31, AID32, AID33, AID34, AID35, AID36, AID37, AID38, AID39, AID40, AID41, AID42, AID43, AID44, AID45, AID46, AID47, AID48, AID49, AID50, AID51, AID52, AID53, AID54, AID55, AID56, AID57, AID58, AID59, AID60, AID61, AID62, AID63, AID64, AID65, AID66, AID67, AID68, AID69, AID70)
AS
(
SELECT prospectid
, MAX(CASE WHEN campaignid = 1 THEN prospectid ELSE NULL END) AS CID1
, MAX(CASE WHEN campaignid = 2 THEN prospectid ELSE NULL END) AS CID2
, MAX(CASE WHEN campaignid = 3 THEN prospectid ELSE NULL END) AS CID3
, MAX(CASE WHEN campaignid = 4 THEN prospectid ELSE NULL END) AS CID4
, MAX(CASE WHEN campaignid = 5 THEN prospectid ELSE NULL END) AS CID5
, MAX(CASE WHEN campaignid = 6 THEN prospectid ELSE NULL END) AS CID6
, MAX(CASE WHEN campaignid = 7 THEN prospectid ELSE NULL END) AS CID7
, MAX(CASE WHEN campaignid = 8 THEN prospectid ELSE NULL END) AS CID8
, MAX(CASE WHEN campaignid = 9 THEN prospectid ELSE NULL END) AS CID9
, MAX(CASE WHEN campaignid = 10 THEN prospectid ELSE NULL END) AS CID10
, MAX(CASE WHEN campaignid = 11 THEN prospectid ELSE NULL END) AS CID11
, MAX(CASE WHEN campaignid = 12 THEN prospectid ELSE NULL END) AS CID12
, MAX(CASE WHEN campaignid = 13 THEN prospectid ELSE NULL END) AS CID13
, MAX(CASE WHEN campaignid = 14 THEN prospectid ELSE NULL END) AS CID14
, MAX(CASE WHEN campaignid = 15 THEN prospectid ELSE NULL END) AS CID15
, MAX(CASE WHEN campaignid = 16 THEN prospectid ELSE NULL END) AS CID16
, MAX(CASE WHEN campaignid = 17 THEN prospectid ELSE NULL END) AS CID17
, MAX(CASE WHEN campaignid = 18 THEN prospectid ELSE NULL END) AS CID18
, MAX(CASE WHEN campaignid = 19 THEN prospectid ELSE NULL END) AS CID19
, MAX(CASE WHEN campaignid = 20 THEN prospectid ELSE NULL END) AS CID20
, MAX(CASE WHEN campaignid = 21 THEN prospectid ELSE NULL END) AS CID21
, MAX(CASE WHEN campaignid = 22 THEN prospectid ELSE NULL END) AS CID22
, MAX(CASE WHEN campaignid = 23 THEN prospectid ELSE NULL END) AS CID23
, MAX(CASE WHEN campaignid = 24 THEN prospectid ELSE NULL END) AS CID24
, MAX(CASE WHEN campaignid = 25 THEN prospectid ELSE NULL END) AS CID25
, MAX(CASE WHEN campaignid = 26 THEN prospectid ELSE NULL END) AS CID26
, MAX(CASE WHEN campaignid = 27 THEN prospectid ELSE NULL END) AS CID27
, MAX(CASE WHEN campaignid = 28 THEN prospectid ELSE NULL END) AS CID28
, MAX(CASE WHEN campaignid = 29 THEN prospectid ELSE NULL END) AS CID29
, MAX(CASE WHEN campaignid = 30 THEN prospectid ELSE NULL END) AS CID30
, MAX(CASE WHEN campaignid = 31 THEN prospectid ELSE NULL END) AS CID31
, MAX(CASE WHEN campaignid = 32 THEN prospectid ELSE NULL END) AS CID32
, MAX(CASE WHEN campaignid = 33 THEN prospectid ELSE NULL END) AS CID33
, MAX(CASE WHEN campaignid = 34 THEN prospectid ELSE NULL END) AS CID34
, MAX(CASE WHEN campaignid = 35 THEN prospectid ELSE NULL END) AS CID35
, MAX(CASE WHEN campaignid = 36 THEN prospectid ELSE NULL END) AS CID36
, MAX(CASE WHEN campaignid = 37 THEN prospectid ELSE NULL END) AS CID37
, MAX(CASE WHEN campaignid = 38 THEN prospectid ELSE NULL END) AS CID38
, MAX(CASE WHEN campaignid = 39 THEN prospectid ELSE NULL END) AS CID39
, MAX(CASE WHEN campaignid = 40 THEN prospectid ELSE NULL END) AS CID40
, MAX(CASE WHEN campaignid = 41 THEN prospectid ELSE NULL END) AS CID41
, MAX(CASE WHEN campaignid = 42 THEN prospectid ELSE NULL END) AS CID42
, MAX(CASE WHEN campaignid = 43 THEN prospectid ELSE NULL END) AS CID43
, MAX(CASE WHEN campaignid = 44 THEN prospectid ELSE NULL END) AS CID44
, MAX(CASE WHEN campaignid = 45 THEN prospectid ELSE NULL END) AS CID45
, MAX(CASE WHEN campaignid = 46 THEN prospectid ELSE NULL END) AS CID46
, MAX(CASE WHEN campaignid = 47 THEN prospectid ELSE NULL END) AS CID47
, MAX(CASE WHEN campaignid = 48 THEN prospectid ELSE NULL END) AS CID48
, MAX(CASE WHEN campaignid = 49 THEN prospectid ELSE NULL END) AS CID49
, MAX(CASE WHEN campaignid = 50 THEN prospectid ELSE NULL END) AS CID50
, MAX(CASE WHEN campaignid = 51 THEN prospectid ELSE NULL END) AS CID51
, MAX(CASE WHEN campaignid = 52 THEN prospectid ELSE NULL END) AS CID52
, MAX(CASE WHEN campaignid = 53 THEN prospectid ELSE NULL END) AS CID53
, MAX(CASE WHEN campaignid = 54 THEN prospectid ELSE NULL END) AS CID54
, MAX(CASE WHEN campaignid = 55 THEN prospectid ELSE NULL END) AS CID55
, MAX(CASE WHEN campaignid = 56 THEN prospectid ELSE NULL END) AS CID56
, MAX(CASE WHEN campaignid = 57 THEN prospectid ELSE NULL END) AS CID57
, MAX(CASE WHEN campaignid = 58 THEN prospectid ELSE NULL END) AS CID58
, MAX(CASE WHEN campaignid = 59 THEN prospectid ELSE NULL END) AS CID59
, MAX(CASE WHEN campaignid = 60 THEN prospectid ELSE NULL END) AS CID60
, MAX(CASE WHEN campaignid = 61 THEN prospectid ELSE NULL END) AS CID61
, MAX(CASE WHEN campaignid = 62 THEN prospectid ELSE NULL END) AS CID62
, MAX(CASE WHEN campaignid = 63 THEN prospectid ELSE NULL END) AS CID63
, MAX(CASE WHEN campaignid = 64 THEN prospectid ELSE NULL END) AS CID64
, MAX(CASE WHEN campaignid = 65 THEN prospectid ELSE NULL END) AS CID65
, MAX(CASE WHEN campaignid = 66 THEN prospectid ELSE NULL END) AS CID66
, MAX(CASE WHEN campaignid = 67 THEN prospectid ELSE NULL END) AS CID67
, MAX(CASE WHEN campaignid = 68 THEN prospectid ELSE NULL END) AS CID68
, MAX(CASE WHEN campaignid = 69 THEN prospectid ELSE NULL END) AS CID69
, MAX(CASE WHEN campaignid = 70 THEN prospectid ELSE NULL END) AS CID70
, MAX(CASE WHEN result = 15 AND campaignid = 1 THEN appointmentdate ELSE NULL END) AS AID1
, MAX(CASE WHEN result = 15 AND campaignid = 2 THEN appointmentdate ELSE NULL END) AS AID2
, MAX(CASE WHEN result = 15 AND campaignid = 3 THEN appointmentdate ELSE NULL END) AS AID3
, MAX(CASE WHEN result = 15 AND campaignid = 4 THEN appointmentdate ELSE NULL END) AS AID4
, MAX(CASE WHEN result = 15 AND campaignid = 5 THEN appointmentdate ELSE NULL END) AS AID5
, MAX(CASE WHEN result = 15 AND campaignid = 6 THEN appointmentdate ELSE NULL END) AS AID6
, MAX(CASE WHEN result = 15 AND campaignid = 7 THEN appointmentdate ELSE NULL END) AS AID7
, MAX(CASE WHEN result = 15 AND campaignid = 8 THEN appointmentdate ELSE NULL END) AS AID8
, MAX(CASE WHEN result = 15 AND campaignid = 9 THEN appointmentdate ELSE NULL END) AS AID9
, MAX(CASE WHEN result = 15 AND campaignid = 10 THEN appointmentdate ELSE NULL END) AS AID10
, MAX(CASE WHEN result = 15 AND campaignid = 11 THEN appointmentdate ELSE NULL END) AS AID11
, MAX(CASE WHEN result = 15 AND campaignid = 12 THEN appointmentdate ELSE NULL END) AS AID12
, MAX(CASE WHEN result = 15 AND campaignid = 13 THEN appointmentdate ELSE NULL END) AS AID13
, MAX(CASE WHEN result = 15 AND campaignid = 14 THEN appointmentdate ELSE NULL END) AS AID14
, MAX(CASE WHEN result = 15 AND campaignid = 15 THEN appointmentdate ELSE NULL END) AS AID15
, MAX(CASE WHEN result = 15 AND campaignid = 16 THEN appointmentdate ELSE NULL END) AS AID16
, MAX(CASE WHEN result = 15 AND campaignid = 17 THEN appointmentdate ELSE NULL END) AS AID17
, MAX(CASE WHEN result = 15 AND campaignid = 18 THEN appointmentdate ELSE NULL END) AS AID18
, MAX(CASE WHEN result = 15 AND campaignid = 19 THEN appointmentdate ELSE NULL END) AS AID19
, MAX(CASE WHEN result = 15 AND campaignid = 20 THEN appointmentdate ELSE NULL END) AS AID20
, MAX(CASE WHEN result = 15 AND campaignid = 21 THEN appointmentdate ELSE NULL END) AS AID21
, MAX(CASE WHEN result = 15 AND campaignid = 22 THEN appointmentdate ELSE NULL END) AS AID22
, MAX(CASE WHEN result = 15 AND campaignid = 23 THEN appointmentdate ELSE NULL END) AS AID23
, MAX(CASE WHEN result = 15 AND campaignid = 24 THEN appointmentdate ELSE NULL END) AS AID24
, MAX(CASE WHEN result = 15 AND campaignid = 25 THEN appointmentdate ELSE NULL END) AS AID25
, MAX(CASE WHEN result = 15 AND campaignid = 26 THEN appointmentdate ELSE NULL END) AS AID26
, MAX(CASE WHEN result = 15 AND campaignid = 27 THEN appointmentdate ELSE NULL END) AS AID27
, MAX(CASE WHEN result = 15 AND campaignid = 28 THEN appointmentdate ELSE NULL END) AS AID28
, MAX(CASE WHEN result = 15 AND campaignid = 29 THEN appointmentdate ELSE NULL END) AS AID29
, MAX(CASE WHEN result = 15 AND campaignid = 30 THEN appointmentdate ELSE NULL END) AS AID30
, MAX(CASE WHEN result = 15 AND campaignid = 31 THEN appointmentdate ELSE NULL END) AS AID31
, MAX(CASE WHEN result = 15 AND campaignid = 32 THEN appointmentdate ELSE NULL END) AS AID32
, MAX(CASE WHEN result = 15 AND campaignid = 33 THEN appointmentdate ELSE NULL END) AS AID33
, MAX(CASE WHEN result = 15 AND campaignid = 34 THEN appointmentdate ELSE NULL END) AS AID34
, MAX(CASE WHEN result = 15 AND campaignid = 35 THEN appointmentdate ELSE NULL END) AS AID35
, MAX(CASE WHEN result = 15 AND campaignid = 36 THEN appointmentdate ELSE NULL END) AS AID36
, MAX(CASE WHEN result = 15 AND campaignid = 37 THEN appointmentdate ELSE NULL END) AS AID37
, MAX(CASE WHEN result = 15 AND campaignid = 38 THEN appointmentdate ELSE NULL END) AS AID38
, MAX(CASE WHEN result = 15 AND campaignid = 39 THEN appointmentdate ELSE NULL END) AS AID39
, MAX(CASE WHEN result = 15 AND campaignid = 40 THEN appointmentdate ELSE NULL END) AS AID40
, MAX(CASE WHEN result = 15 AND campaignid = 41 THEN appointmentdate ELSE NULL END) AS AID41
, MAX(CASE WHEN result = 15 AND campaignid = 42 THEN appointmentdate ELSE NULL END) AS AID42
, MAX(CASE WHEN result = 15 AND campaignid = 43 THEN appointmentdate ELSE NULL END) AS AID43
, MAX(CASE WHEN result = 15 AND campaignid = 44 THEN appointmentdate ELSE NULL END) AS AID44
, MAX(CASE WHEN result = 15 AND campaignid = 45 THEN appointmentdate ELSE NULL END) AS AID45
, MAX(CASE WHEN result = 15 AND campaignid = 46 THEN appointmentdate ELSE NULL END) AS AID46
, MAX(CASE WHEN result = 15 AND campaignid = 47 THEN appointmentdate ELSE NULL END) AS AID47
, MAX(CASE WHEN result = 15 AND campaignid = 48 THEN appointmentdate ELSE NULL END) AS AID48
, MAX(CASE WHEN result = 15 AND campaignid = 49 THEN appointmentdate ELSE NULL END) AS AID49
, MAX(CASE WHEN result = 15 AND campaignid = 50 THEN appointmentdate ELSE NULL END) AS AID50
, MAX(CASE WHEN result = 15 AND campaignid = 51 THEN appointmentdate ELSE NULL END) AS AID51
, MAX(CASE WHEN result = 15 AND campaignid = 52 THEN appointmentdate ELSE NULL END) AS AID52
, MAX(CASE WHEN result = 15 AND campaignid = 53 THEN appointmentdate ELSE NULL END) AS AID53
, MAX(CASE WHEN result = 15 AND campaignid = 54 THEN appointmentdate ELSE NULL END) AS AID54
, MAX(CASE WHEN result = 15 AND campaignid = 55 THEN appointmentdate ELSE NULL END) AS AID55
, MAX(CASE WHEN result = 15 AND campaignid = 56 THEN appointmentdate ELSE NULL END) AS AID56
, MAX(CASE WHEN result = 15 AND campaignid = 57 THEN appointmentdate ELSE NULL END) AS AID57
, MAX(CASE WHEN result = 15 AND campaignid = 58 THEN appointmentdate ELSE NULL END) AS AID58
, MAX(CASE WHEN result = 15 AND campaignid = 59 THEN appointmentdate ELSE NULL END) AS AID59
, MAX(CASE WHEN result = 15 AND campaignid = 60 THEN appointmentdate ELSE NULL END) AS AID60
, MAX(CASE WHEN result = 15 AND campaignid = 61 THEN appointmentdate ELSE NULL END) AS AID61
, MAX(CASE WHEN result = 15 AND campaignid = 62 THEN appointmentdate ELSE NULL END) AS AID62
, MAX(CASE WHEN result = 15 AND campaignid = 63 THEN appointmentdate ELSE NULL END) AS AID63
, MAX(CASE WHEN result = 15 AND campaignid = 64 THEN appointmentdate ELSE NULL END) AS AID64
, MAX(CASE WHEN result = 15 AND campaignid = 65 THEN appointmentdate ELSE NULL END) AS AID65
, MAX(CASE WHEN result = 15 AND campaignid = 66 THEN appointmentdate ELSE NULL END) AS AID66
, MAX(CASE WHEN result = 15 AND campaignid = 67 THEN appointmentdate ELSE NULL END) AS AID67
, MAX(CASE WHEN result = 15 AND campaignid = 68 THEN appointmentdate ELSE NULL END) AS AID68
, MAX(CASE WHEN result = 15 AND campaignid = 69 THEN appointmentdate ELSE NULL END) AS AID69
, MAX(CASE WHEN result = 15 AND campaignid = 70 THEN appointmentdate ELSE NULL END) AS AID70
FROM peter.dbo.ProspectsInCampaigns
GROUP BY prospectid
)
--SELECT * FROM CTE INNER JOIN dbo.prospects P ON P.id = CTE.prospectid
UPDATE P SET
P.campaign1 = CTE.CID1
, P.campaign2 = CTE.CID2
, P.campaign3 = CTE.CID3
, P.campaign4 = CTE.CID4
, P.campaign5 = CTE.CID5
, P.campaign6 = CTE.CID6
, P.campaign7 = CTE.CID7
, P.campaign8 = CTE.CID8
, P.campaign9 = CTE.CID9
, P.campaign10 = CTE.CID10
, P.campaign11 = CTE.CID11
, P.campaign12 = CTE.CID12
, P.campaign13 = CTE.CID13
, P.campaign14 = CTE.CID14
, P.campaign15 = CTE.CID15
, P.campaign16 = CTE.CID16
, P.campaign17 = CTE.CID17
, P.campaign18 = CTE.CID18
, P.campaign19 = CTE.CID19
, P.campaign20 = CTE.CID20
, P.campaign21 = CTE.CID21
, P.campaign22 = CTE.CID22
, P.campaign23 = CTE.CID23
, P.campaign24 = CTE.CID24
, P.campaign25 = CTE.CID25
, P.campaign26 = CTE.CID26
, P.campaign27 = CTE.CID27
, P.campaign28 = CTE.CID28
, P.campaign29 = CTE.CID29
, P.campaign30 = CTE.CID30
, P.campaign31 = CTE.CID31
, P.campaign32 = CTE.CID32
, P.campaign33 = CTE.CID33
, P.campaign34 = CTE.CID34
, P.campaign35 = CTE.CID35
, P.campaign36 = CTE.CID36
, P.campaign37 = CTE.CID37
, P.campaign38 = CTE.CID38
, P.campaign39 = CTE.CID39
, P.campaign40 = CTE.CID40
, P.campaign41 = CTE.CID41
, P.campaign42 = CTE.CID42
, P.campaign43 = CTE.CID43
, P.campaign44 = CTE.CID44
, P.campaign45 = CTE.CID45
, P.campaign46 = CTE.CID46
, P.campaign47 = CTE.CID47
, P.campaign48 = CTE.CID48
, P.campaign49 = CTE.CID49
, P.campaign50 = CTE.CID50
, P.campaign51 = CTE.CID51
, P.campaign52 = CTE.CID52
, P.campaign53 = CTE.CID53
, P.campaign54 = CTE.CID54
, P.campaign55 = CTE.CID55
, P.campaign56 = CTE.CID56
, P.campaign57 = CTE.CID57
, P.campaign58 = CTE.CID58
, P.campaign59 = CTE.CID59
, P.campaign60 = CTE.CID60
, P.campaign61 = CTE.CID61
, P.campaign62 = CTE.CID62
, P.campaign63 = CTE.CID63
, P.campaign64 = CTE.CID64
, P.campaign65 = CTE.CID65
, P.campaign66 = CTE.CID66
, P.campaign67 = CTE.CID67
, P.campaign68 = CTE.CID68
, P.campaign69 = CTE.CID69
, P.campaign70 = CTE.CID70
, P.appointmentdate1 = CTE.AID1
, P.appointmentdate2 = CTE.AID2
, P.appointmentdate3 = CTE.AID3
, P.appointmentdate4 = CTE.AID4
, P.appointmentdate5 = CTE.AID5
, P.appointmentdate6 = CTE.AID6
, P.appointmentdate7 = CTE.AID7
, P.appointmentdate8 = CTE.AID8
, P.appointmentdate9 = CTE.AID9
, P.appointmentdate10 = CTE.AID10
, P.appointmentdate11 = CTE.AID11
, P.appointmentdate12 = CTE.AID12
, P.appointmentdate13 = CTE.AID13
, P.appointmentdate14 = CTE.AID14
, P.appointmentdate15 = CTE.AID15
, P.appointmentdate16 = CTE.AID16
, P.appointmentdate17 = CTE.AID17
, P.appointmentdate18 = CTE.AID18
, P.appointmentdate19 = CTE.AID19
, P.appointmentdate20 = CTE.AID20
, P.appointmentdate21 = CTE.AID21
, P.appointmentdate22 = CTE.AID22
, P.appointmentdate23 = CTE.AID23
, P.appointmentdate24 = CTE.AID24
, P.appointmentdate25 = CTE.AID25
, P.appointmentdate26 = CTE.AID26
, P.appointmentdate27 = CTE.AID27
, P.appointmentdate28 = CTE.AID28
, P.appointmentdate29 = CTE.AID29
, P.appointmentdate30 = CTE.AID30
, P.appointmentdate31 = CTE.AID31
, P.appointmentdate32 = CTE.AID32
, P.appointmentdate33 = CTE.AID33
, P.appointmentdate34 = CTE.AID34
, P.appointmentdate35 = CTE.AID35
, P.appointmentdate36 = CTE.AID36
, P.appointmentdate37 = CTE.AID37
, P.appointmentdate38 = CTE.AID38
, P.appointmentdate39 = CTE.AID39
, P.appointmentdate40 = CTE.AID40
, P.appointmentdate41 = CTE.AID41
, P.appointmentdate42 = CTE.AID42
, P.appointmentdate43 = CTE.AID43
, P.appointmentdate44 = CTE.AID44
, P.appointmentdate45 = CTE.AID45
, P.appointmentdate46 = CTE.AID46
, P.appointmentdate47 = CTE.AID47
, P.appointmentdate48 = CTE.AID48
, P.appointmentdate49 = CTE.AID49
, P.appointmentdate50 = CTE.AID50
, P.appointmentdate51 = CTE.AID51
, P.appointmentdate52 = CTE.AID52
, P.appointmentdate53 = CTE.AID53
, P.appointmentdate54 = CTE.AID54
, P.appointmentdate55 = CTE.AID55
, P.appointmentdate56 = CTE.AID56
, P.appointmentdate57 = CTE.AID57
, P.appointmentdate58 = CTE.AID58
, P.appointmentdate59 = CTE.AID59
, P.appointmentdate60 = CTE.AID60
, P.appointmentdate61 = CTE.AID61
, P.appointmentdate62 = CTE.AID62
, P.appointmentdate63 = CTE.AID63
, P.appointmentdate64 = CTE.AID64
, P.appointmentdate65 = CTE.AID65
, P.appointmentdate66 = CTE.AID66
, P.appointmentdate67 = CTE.AID67
, P.appointmentdate68 = CTE.AID68
, P.appointmentdate69 = CTE.AID69
, P.appointmentdate70 = CTE.AID70
--SELECT * FROM
FROM CTE INNER JOIN dbo.prospects P ON P.id = CTE.prospectid
SELECT * FROM dbo.prospects
ROLLBACK TRAN
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply