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.
| Author |
Topic |
|
tad
Starting Member
31 Posts |
 Posted - 2002-08-27 : 14:02:38
|
| I would like to append missing rows into a results table for a given item.The results table has an item, test, property and resultThe spec_detail table has an spec, test, propertyI need to insert into the results table a row for each spec_detail table test and property not currently in the results table.I have a parameter for the item on results table and a spec for the spec_detail table.I was trying with no luck.INSERT INTO RESULT ( ITEM_NO, TEST_NO, PROPERTY_NO ) SELECT @pITEM_NO, COA_SPEC_DETAIL.TEST_NO, COA_SPEC_DETAIL.PROPERTY_NO, FROM COA_SPEC_DETAIL LEFT JOIN COA_LOT_RESULT ON COA_LOT_RESULT.TEST_NO <> COA_SPEC_DETAIL.TEST_NO AND COA_LOT_RESULT.PROPERTY_NO <> COA_SPEC_DETAIL.PROPERTY_NOWHERE COA_LOT_RESULT.ITEM_NO = @pITEM_NO AND COA_SPEC_DETAIL.SPEC_NO = @pSPEC_NOAny advice? |
|
|
tad
Starting Member
31 Posts |
Posted - 2002-08-27 : 14:32:06
|
| I've figured out a solution, but would like to hear any comments to this approach.Thanks INSERT INTO COA_LOT_RESULT ( ITEM_NO, LOT_NO, GRADE_NO, SAMPLESET, TEST_NO, PROPERTY_NO ) SELECT @pITEM_NO, @pSPEC_NO, @pGRADE_NO, @pSAMPLESET, COA_SPEC_DETAIL.TEST_NO, COA_SPEC_DETAIL.PROPERTY_NO FROM COA_SPEC_DETAIL WHERE COA_SPEC_DETAIL.SPEC_NO = @pSPEC_NO AND COA_SPEC_DETAIL.RSLT_FLAG IN ('O','R') AND COA_SPEC_DETAIL.COA_SPEC_DETAILID NOT IN ( SELECT COA_SPEC_DETAILID FROM COA_SPEC_DETAIL INNER JOIN COA_LOT_RESULT ON COA_LOT_RESULT.TEST_NO = COA_SPEC_DETAIL.TEST_NO AND COA_LOT_RESULT.PROPERTY_NO = COA_SPEC_DETAIL.PROPERTY_NO WHERE COA_LOT_RESULT.ITEM_NO = @pITEM_NO AND COA_LOT_RESULT.LOT_NO = @pLOT_NO AND COA_LOT_RESULT.GRADE_NO = @pGRADE_NO AND COA_LOT_RESULT.SAMPLESET = @pSAMPLESET AND COA_SPEC_DETAIL.SPEC_NO = @pSPEC_NO) |
 |
|
|
|
|
|
|
|
Confusing Query