June 6, 2011 at 4:24 am
Dear All,
I have 2 tables 1. SrcTable & 2. destTable (#temp Table). I am trying to copy all rows from srcTable to destTable and ignore the duplicates.
See the code:
INSERT INTO #temp (item_code,variant_code)
SELECT T2.psp_item_no,T2.psp_item_var FROM pmddb..pmd_mpsp_ps_postn T2 WHERE T2.psp_io_flag = 'i'
I want that #temp would not contain duplicate data (item_code,variant_code)
how to achieve that ?
June 6, 2011 at 4:29 am
Place distinct between select and first column.
June 6, 2011 at 4:30 am
DISTINCT should do:
INSERT INTO #temp (item_code,variant_code)
SELECT DISTINCT T2.psp_item_no,T2.psp_item_var FROM pmddb..pmd_mpsp_ps_postn T2 WHERE T2.psp_io_flag = 'i'
Hope this helps
Gianluca
-- Gianluca Sartori
June 6, 2011 at 4:37 am
This is true but I don't want SELECT only Distinct from the Source Table to insert.
I want only distinct record will be inserted into the Destination Table.
How is it possible ?
June 6, 2011 at 5:32 am
This cod is working : :w00t::w00t:
INSERT INTO #temp (item_code,variant_code)
SELECT T2.psp_item_no,T2.psp_item_var FROM pmddb..pmd_mpsp_ps_postn T2 WHERE
T2.psp_ps_no = @BomNo2 and T2.psp_io_flag = 'i' and
NOT EXISTS(SELECT * FROM #temp t3
WHERE (psp_item_no = item_code AND
psp_item_var= variant_code)
)
PLz. see ::hehe::hehe:
WHERE (psp_item_no = item_code AND
psp_item_var= variant_code)
) [/code]
June 6, 2011 at 5:45 am
Here you may *also* need to have a left join (Temp table and Destination)while inserting where keys are equal and destination table has null values for mismatch records.
June 6, 2011 at 5:48 am
INSERT INTO #temp (item_code,variant_code)
SELECT DISTINCT T2.psp_item_no,T2.psp_item_var
FROM pmddb..pmd_mpsp_ps_postn T2
WHERE T2.psp_io_flag = 'i'
AND NOT EXISTS (
SELECT 1
FROM #temp AS T1
WHERE T1.item_code = T2.psp_item_no
AND T1.variant_code = T2.psp_item_var
)
-- Gianluca Sartori
June 6, 2011 at 5:52 am
Gianluca Sartori (6/6/2011)
INSERT INTO #temp (item_code,variant_code)
SELECT DISTINCT T2.psp_item_no,T2.psp_item_var
FROM pmddb..pmd_mpsp_ps_postn T2
WHERE T2.psp_io_flag = 'i'
AND NOT EXISTS (
SELECT 1
FROM #temp AS T1
WHERE T1.item_code = T2.psp_item_no
AND T1.variant_code = T2.psp_item_var
)
Thanks a lot..:-D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply