Author |
Topic |
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-27 : 11:10:32
|
HiI have a sql table called ProductVariant. Within this table there is a column called SalePrice. Within another SQL table called ExtendedPrice I have the following rowsExtendedPriceIDVariantIDCustomerLevel IDPriceExtension DataCreated OnUpdated OnBoth SQL tables are joined by the column Variant IDWithin the Extended Price table I have two Customer Levels with values of 5 and 7What I want to try to do is get a SQL script that copies any SalePrice that does not have a value of NULL and then creates a record in the ExtendedPrice tableExampleProduct Variant 999 has a SalePrice of 6.66The SQL Script with then createtwo records with the Product Variant as 999 and the SalePrice of 6.66But two records for each Customer Level |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-28 : 01:51:48
|
something like this?INSERT ExtendedPrice (VariantID,CustomerLevelID,Price,[Created On],[Updated On])SELECT p.VariantID,c.Custlevel,p.SalePrice,GETDATE(),GETDATE()FROM ProductVariant pCROSS JOIN ( SELECT 5 AS CustLevel UNION ALL SELECT 7 )c i assume ExtendedPriceID is of identity type. i've not included Extension Data field as i dont know what value you need to populate for it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-28 : 10:40:30
|
Thanks. When I run this it becomesMsg 515, Level 16, State 2, Line 2Cannot insert the value NULL into column 'Price', table 'dectest.dbo.ExtendedPrice'; column does not allow nulls. INSERT fails.The statement has been terminated.The Extended Price ID will be a incremental value 1,2,3,4,5 etc for each record. Extension Data will always Be NULL. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-28 : 11:22:15
|
that means you've NULL values for SalePrice in your ProductVariant tabletry this to ignore themINSERT ExtendedPrice (VariantID,CustomerLevelID,[Extension Data],Price,[Created On],[Updated On])SELECT p.VariantID,c.Custlevel,NULL,p.SalePrice,GETDATE(),GETDATE()FROM ProductVariant pCROSS JOIN ( SELECT 5 AS CustLevel UNION ALL SELECT 7 )cWHERE p.SalePrice IS NOT NULL ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-12-28 : 13:21:13
|
Thanks! |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-28 : 13:28:57
|
Thanks for all your help.If I wanted to insert the Product ID from product.dbo into ProductCustomerLevel and then have the following columns changed what would the SQL be for that?ProductIDCustomerLevelWhat I want to try is to insert every ProductID from product.dbo into ProductCustomerLevel and then have the CustomerLevel set to 5 and 7 for each ProductIDWithin ProductCustomerLevel ProductID is the first columnThanks for all your help!!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-29 : 12:44:14
|
quote: Originally posted by raindear Thanks for all your help.If I wanted to insert the Product ID from product.dbo into ProductCustomerLevel and then have the following columns changed what would the SQL be for that?ProductIDCustomerLevelWhat I want to try is to insert every ProductID from product.dbo into ProductCustomerLevel and then have the CustomerLevel set to 5 and 7 for each ProductIDWithin ProductCustomerLevel ProductID is the first columnThanks for all your help!!!
same as how i showed in previous suggestionINSERT ProductCustomerLevel (ProductID,CustomerLevel)SELECT ProductID,CustomerLevelFROM dbo.Product pCROSS JOIN ( SELECT 5 AS CustomerLevel UNION ALL SELECT 7 )c Assuming any other columns if present in ProductCustomerLevel are of NULLable type.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-12-30 : 06:20:56
|
That is awesome thanks!If I wanted to run this again and avoid this errorCannot insert duplicate key in objectwhere would I insert the where not exists clause in these? |
|
|
|