INSERT during CASE statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT during CASE statement

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-03 : 11:20:03

Hi Guys

I am using the following query but it is failing:

INSERT INTO SESSALESQTYRESTRICTIONS
(
ItemRelation
,MaxQty
,CustCode

)

SELECT
v.[Part Number]
,CASE WHEN v.[Quantity] <> 0 THEN (INSERT INTO SESSALESQTYRESTRICTIONS
(
ItemRelation
,MaxQty
)
SELECT
v.[Part Number]
,v.[Quantity]
FROM v_ItemDetails AS v
)
ELSE v.[Quantity]
END
,2
FROM v_ItemDetails AS v

The thing I am trying to do is if the Quantity is not the same 0 to Insert the record again so there are two of them. Is this possible to do using CASE?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 11:24:28

[code]INSERT INTO SESSALESQTYRESTRICTIONS
(
ItemRelation
,MaxQty
,CustCode

)

SELECT
v.[Part Number],
v.[Quantity],
2
FROM v_ItemDetails AS v
WHERE v.[Quantity]=0

INSERT INTO SESSALESQTYRESTRICTIONS
(
ItemRelation
,MaxQty
,CustCode

)

SELECT
v.[Part Number],
v.[Quantity],
2
FROM v_ItemDetails AS v
CROSS JOIN (SELECT 1 AS ID
UNION ALL
SELECT 2)tmp
WHERE v.[Quantity]<>0[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 11:41:04

or combine them

INSERT INTO SESSALESQTYRESTRICTIONS
(
ItemRelation
,MaxQty
,CustCode

)
SELECT
v.[Part Number],
v.[Quantity],
2
FROM v_ItemDetails AS v
CROSS JOIN (SELECT 1 AS ID 
            UNION ALL
            SELECT 2)tmp
WHERE (v.[Quantity]<>0
OR tmp.ID=1)