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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 joining / inserting tables

Author  Topic 

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-12-27 : 11:10:32
Hi

I 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 rows

ExtendedPriceID
VariantID
CustomerLevel ID
Price
Extension Data
Created On
Updated On

Both SQL tables are joined by the column Variant ID

Within the Extended Price table I have two Customer Levels with values of 5 and 7

What 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 table

Example

Product Variant 999 has a SalePrice of 6.66

The SQL Script with then create

two records with the Product Variant as 999 and the SalePrice of 6.66

But 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 p
CROSS 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-12-28 : 10:40:30
Thanks. When I run this it becomes

Msg 515, Level 16, State 2, Line 2
Cannot 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.

Go to Top of Page

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 table
try this to ignore them

INSERT ExtendedPrice
(
VariantID,
CustomerLevelID,
[Extension Data],
Price,
[Created On],
[Updated On]
)
SELECT p.VariantID,
c.Custlevel,
NULL,
p.SalePrice,
GETDATE(),
GETDATE()
FROM ProductVariant p
CROSS JOIN (
SELECT 5 AS CustLevel UNION ALL
SELECT 7
)c
WHERE p.SalePrice IS NOT NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-12-28 : 13:21:13
Thanks!
Go to Top of Page

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?

ProductID
CustomerLevel

What 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 ProductID

Within ProductCustomerLevel ProductID is the first column

Thanks for all your help!!!
Go to Top of Page

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?

ProductID
CustomerLevel

What 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 ProductID

Within ProductCustomerLevel ProductID is the first column

Thanks for all your help!!!


same as how i showed in previous suggestion

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevel
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c

Assuming any other columns if present in ProductCustomerLevel are of NULLable type.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 error

Cannot insert duplicate key in object

where would I insert the where not exists clause in these?
Go to Top of Page
   

- Advertisement -