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 |
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
 Posted - 2008-08-17 : 14:10:54
|
| Hi GuysI am having trouble making a stored procedure with the following scenario.I have two tables, WebStock and ProductVariant. Shown below with sample data: (please ignore the links, just used for example data)Webstock:WebID, InternetOnly, InternetAddress1,Yes,www.sample.com2,Yes,www.sample.com3,No,,4,No,www.sample.com5.No,,6. No,www.sample.com7. No,www.sample.comProductVariantVariantID, ProductID,FormatID,InternetAddress1,1,3,www.sample.com2,2,3,www.sample.com3,3,1,,4,4,1,www.sample.com5,4,3,www.sample.com6,5,1,,7,6,1,www.sample.com8,6,3,www.sample.com9,7,3,www.sample.com10,7,3,www.sample.comThe WebID in the Webstock table is linked to the ProductID in the Product Variant table.The Webstock table is a form of temp table where the table is truncated and data is imported to and then transferred to the ProductVariant table.When the item in the Webstock table has an InternetOnly value of NO and no value in the Internet it is entered into the ProductVariant table with a FormatID value of 1.When the item in the Webstock table has an InternetOnly value of YES and HAS a value in the InternetAddress it is entered into the ProductVariant table with a FormatID value of 3.When the item in the Webstock table has an InternetOnly value of No and HAS a value in the InternetAddress it is entered into the ProductVariant table twice one with with a FormatID value of 1 and the other with a FormatID value of 3.The thing I am stuck on is updating. The Webstock table may contain updated information on a product meaning that the ProductVariant table will have to be amended.Here is an example:The current import table shows that the product was previously entered with an InternetOnly value of NO and the InternetAddress had a value (I am checking this using LEN(InternetAddress)>1). This would mean that the record would be entered into the ProductVariant twice but with different FormatIDs, these being 1 and 3. The new import file now stated that the product now had an InternetOnly value of YES and the InternetAddress had a value. The action that would have to take place is Update the EmailAddress and Delete the record for the product where the FormatID is equal to 1. I am trying to put this into a procedure but not sure how, I was thinking of using an IF statement but not sure how to.There are 3 conditions that I am looking at, these being:1:InternetOnly is YESInternet Address contains a valuePrevious InternetOnly was NOPrevious InternetAddress contains a valueAction to ProductVariant Table: Update EmailAddress Delete the record for the product where the FormatID is 1.2:InternetOnly is YESInternet Address contains a valuePrevious InternetOnly was NOPrevious InternetAddress contains NO VALUEAction to ProductVariant Table: Update EmailAddress Delete the record for the product where the FormatID is 1. Insert a record for the product with a FormatID equal to 3.3:InternetOnly is NOInternet Address contains NO VALUEPrevious InternetOnly was NOPrevious InternetAddress contains a valueAction to ProductVariant Table: Update EmailAddress Delete the record for the product where the FormatID is 3. Insert a record for the product with a FormatID equal to 1.I hope this make sense!!!Is there a way of doing all the above in one procedure?Thanking you in advance!!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 00:35:52
|
| How are you planning to do this? will you be trying to do this updation periodically or whenever data in webstock table changes? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 00:42:17
|
| Also do you have an audit/ history table? Does your Webstock table have an audit column like datecreate,lastmodified...? |
|
|
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-08-18 : 06:32:26
|
| HiI'm am planning to update it whenever a request comes over, so it can come at different times.Also there is no audit type column such as datecreated.I hope this helps!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 06:44:14
|
quote:
Originally posted by rcr69er
HiI'm am planning to update it whenever a request comes over, so it can come at different times.Also there is no audit type column such as datecreated.I hope this helps!!!
Then how will you identify the latest inserted records of WebStock? |
|
|
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-08-18 : 12:20:22
|
| HiI found an easily solution. Just truncate the ProductVariant table and import the data again each time! :D |
|
|
|
|
|
|
|
|
I'm Stuck!!!