INSERT Query

Neal · May 2nd, 2007, 07:49 AM

Hi,

I have several fields from Table1 which are populating fields in Table2. In fields 2 & 3 in Table2, I need to populate one or the other dependant on the value of the 1st character in Field4 in Table1.

i.e.
Table1 Field4 = '3xxxx'
then enter value in Table2 Field2
If NOT beginning with '3'
then enter value in Table2 Field3

Code:

INSERT INTO dbo.MER_Sales
(
dbo.MER_Sales.ORDER_ID,
dbo.MER_Sales.MERCHANT,
dbo.MER_Sales.PAYER,
)
SELECT dbo.Progress_salord.[Order Number],
SubString(dbo.progress_cust.[Customer Reference],1,1) = '3', 
SubString(dbo.progress_cust.[Customer Reference],1,1) <> '3'
FROM dbo.Progress_salord INNER JOIN dbo.progress_alloc ON
dbo.Progress_salord.[Sale Or Purchase] = dbo.progress_alloc.[Destination Type]

How do I achieve this?

Thanks in advance,

Neal

A Northern Soul

happygv · May 2nd, 2007, 08:35 AM

I think this would do...

Code:

INSERT INTO dbo.MER_Sales
(
dbo.MER_Sales.ORDER_ID,
--dbo.MER_Sales.MERCHANT,
--dbo.MER_Sales.PAYER,
)
SELECT dbo.Progress_salord.[Order Number],
CASE WHEN SubString(dbo.progress_cust.[Customer Reference],1,1) = '3' 
    THEN <Your_Value> ELSE NULL END,
CASE WHEN SubString(dbo.progress_cust.[Customer Reference],1,1) <> '3' 
    THEN <Your_Value> ELSE NULL END
FROM dbo.Progress_salord INNER JOIN dbo.progress_alloc ON
dbo.Progress_salord.[Sale Or Purchase] = dbo.progress_alloc.[Destination Type]

Cheers.

_________________________
- Vijay G
Strive for Perfection

Neal · May 3rd, 2007, 05:18 PM

Cheers.

Thanks very much.

Neal

A Northern Soul