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.
| Author |
Topic |
|
jubilanttiger
Starting Member
19 Posts |
 Posted - 2005-05-20 : 08:51:01
|
Hi,Application : Sql Server 2000I have a Campaign Participation table whose structure is something like this,FirstName LastName Year1CampaignID Year2CampaignIDWhat I am trying to do is as the following steps, 1. First I run a query to get the FirstName, LastName and Year1CampaignID of all members who participated in the Year1Campaign.2. Now I run another query to retrieve all the members who participated in the Year2Campaign.3. The results of Step #2 query would include members who also participated in the Year1Campaign, so in such a case I need to update the Year2CampaignID for the members whose names are already there in the Campaign Participation table.4. The results of Step #2 query would also include members who did not participate in the Year1Campaign, so in such a case I need to insert the member's name and their Year2CampaignID.I think this is something like a conditional insert or update.I am able to complete Steps #1 and Step #2. But not able to do Step#3 and Step #4. Do u have any suggestions of how I can go about implementing it. If you can show me some sample code, it would be very helpful.....Thanks for all those who read my post and replied...Your replies would help me progress thru my project... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-20 : 09:01:52
|
| update Participation set Year2CampaignID = t2.Year2CampaignIDfrom Participation pjoin #tmp t2on p.FirstName = t2.FirstName and p.LastName = t2.LastName insert Participation (FirstName, LastName, Year1CampaignID, Year2CampaignID)select t2.FirstName, t2.LastName, null, t2.Year2CampaignIDfrom #tmp t2left join Participation pon p.FirstName = t2.FirstName and p.LastName = t2.LastName where p.FirstName is null==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jubilanttiger
Starting Member
19 Posts |
Posted - 2005-05-20 : 09:15:37
|
| Thanks a lot for your reply...I think your query should do my job. But I have not yet implemented it and would be testing it too very soon (Say should get the implementation and testing done within a couple of hours). So if you can continue checking this post for a couple of hours, it would be grr88.Meanwhile I have a question regarding your query. I see that in your insert statement you have this condition "where p.FirstName is null". I think I understand what you are trying to do... but can you explain what is the purpose of this condition... ???Thanks a lot for replying... |
|
|
|
|
|
|
|
|
Conditional insert or update in SQL Server