(urgent) Help needed for this insert statement

Hi,

I am trying to insert the Source name and clientId to a table called clientSource...

The User will send in some Dbf File... So in a particular file called PlanDbf.. I have the following fields

PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name... SRC20Name

170234 Constructions Employee Deferral Employer Discretionary Employer Matching...

And in another table called SourceDBF i have the following fields with data

PlanNumber PARTID SOURCE_NUM etc...

170234 123456789 1

170234 123456789 3

170234 451231334 1

So how do i match the Source_NUM with SRCnames when i insert it into the table..

INSERT INTO Statement..ClientSource
(
@.ClientId,
SourceName
)
SELECT
s.SOURCE_NUM

 FROM SourceDBF ..

but i am stuck..

any help will be appreciated.

Regards

Karen

I cant see a connection between them. How are they related?

|||

The 2 tables are related by the plan number...

and will know by taking a look at the PlanDBF if its 1 then to pull ... SRC1Name...

|||

Sorry I still cant get it.. can you just post few rows from each tables and expected output so its easier to understand (at least for me)..

|||

this is how the table that i need to insert is ...

SourceId (int identity) ClientID SourceName

26 1 DEFERRAL27 1 EMPLOYER28 1 MATCHING29 1 ROLLUnre30 1 UNCASHED31 1 ROLL 32 5 DEFERRAL33 5 ROLL 34 5 EMPLOYER35 5 MATCHING

And i will be getting the Data the source name from the Table...

PlanDBF (which has one row of data)

PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name... SRC20Name

170234 Constructions Employee Deferral Employer Discretionary Employer Matching...

and then once i insert the data to the above table called source.. I am inserting the data from the Source Summary which is as follows

PlanNumber PartId Source_NUM begbal dep withdrawels etc..

170925 1296027 1 6211.63 325.00 0.00 211.03 0.00 6747.66 100.06747.66 True170925 1296027 3 2887.36 0.00 0.00 108.38 0.00 2995.74 80.002396.59 True170925 1486037 1 174.64 0.00 0.00 11.13 0.00 185.77 100.0185.77 True170925 1733690 1 2396.84 1625.00 0.00 181.37 0.00 4203.21 100.04203.21 True170925 1733690 3 770.72 0.00 0.00 49.89 0.00 820.61 0.000.00 True170925 2294760 1 4870.82 650.00 0.00 282.52 0.00 5803.34 100.05803.34 True170925 2294760 3 2972.46 0.00 0.00 148.26 0.00 3120.72 20.00624.15 True170925 2443743 1 1421.74 650.00 0.00 42.43 0.00 2114.17 100.02114.17 True170925 2443743 3 611.00 0.00 0.00 18.16 0.00 629.16 20.00125.83 True170925 2662592 1 2857.15 1300.00 0.00 131.71 0.00 4288.86 100.04288.86 True

to another table ParticipantSourceSummary which has the has the follow fields

Plan Number PartId SourceId (which will be got from the clientSource table) and the remaining fields from the SourceSummary table

So at the end while i generate a report i want Source_NUm 1 to be whats in SRCName and so on

Hope this is clear.

Regards

Karen

|||

CREATE VIEW vw_Sources AS

SELECT PlanNumber,Src1Name AS SourceName,1 AS Source_Num

FROM PlanDBF

UNION ALL

SELECT PlanNumber,Src2Name AS SourceName,2 AS Source_Num

FROM PlanDBF

UNION ALL

SELECT PlanNumber,Src3Name AS SourceName,3 AS Source_Num

FROM PlanDBF

{repeat 17 more times}

--

Not sure of all your relations, but then you can do something like this:

SELECT s.PlanNumber,s.PartID,s.Source_Num,vw_Sources.SourceName

FROM SourceDBF s

JOIN vw_Sources ON s.PlanNumber=vw_Sources.PlanNumber AND s.SOURCE_NUM=vw_Sources.SOURCE_NUM

|||

Alternatively, you can do this:

SELECT s.PlanNUmber,s.PartID,s.Source_Num,

(SELECT CASE s.Source_Num WHEN 1 THEN Src1Name WHEN 2 THEN Src2Name WHEN 3 THEN Src3Name ... END FROM PlanDBF p WHERE p.PlanNumber=s.PlanNumber) AS SourceName

FROM SourceDBF

|||

Motley,

Thanks a lot.. the solution works for me

Regards,

Karen