January 15, 2009 at 7:56 am
Hello, with the following command I am trying to insert records into a destination table named tracks. The data is coming from two source tables (GRProductTrack and GRRecording). Both source tables have the potential to contain records iwth duplicate values for the fields that I'm interested in, hence the DISTINCT clause.
INSERT
tracks
(item_code,
GRProductID,
GRRecordingID,
component,
side,
position,
title,
duration,
isrc_code,
SourceOfData)
SELECT distinct
items.code as item_code,
GRProductTrack.GRProductID,
GRProductTrack.GRRecordingID,
GRProductTrack.component,
GRProductTrack.side,
GRProductTrack.position,
GRRecording.title,
GRProductTrack.duration,
GRProductTrack.isrc_code,
'GR'
FROM
GRProductTrack
INNER JOIN
GRRecording ON GRProductTrack.GRRecordingID = GRRecording.GRRecordingID
INNER JOIN
items ON GRProductTrack.GRProductID = items.GRProductID
WHERE
NOT EXISTS
(SELECT NULL from tracks
WHERE grproducttrack.grProductID = tracks.GRProductID
AND grproducttrack.grRecordingID = tracks.GRRecordingID)
The issue I'm having is with the WHERE part of this. The idea is that I only want to insert records which are not already present in the tracks table. This is determined by the grProductID and grRecordingID for the two tables. For a valid insert the tracks table may contain a record with the same grProductID or the same grRecordingID (as in the source tables), but NOT the same combination of both.
I had thought that the above query was working as described, but I am now finding that some records are not being inserted and it seems to stem from the fact that the destination table has records present that have the same grProductID but different grRecordingID (and vice versa). Can someone help me with this? Thanks,
Tom
January 15, 2009 at 8:37 am
Can you do something as follows (Verify before you run this code in prod :))
INSERT
tracks
(item_code,
GRProductID,
GRRecordingID,
component,
side,
position,
title,
duration,
isrc_code,
SourceOfData)
SELECT distinct
items.code as item_code,
GRProductTrack.GRProductID,
GRProductTrack.GRRecordingID,
GRProductTrack.component,
GRProductTrack.side,
GRProductTrack.position,
GRRecording.title,
GRProductTrack.duration,
GRProductTrack.isrc_code,
'GR'
FROM
GRProductTrack
INNER JOIN
GRRecording ON GRProductTrack.GRRecordingID = GRRecording.GRRecordingID
INNER JOIN
items ON GRProductTrack.GRProductID = items.GRProductID
--
LEFT OUTER JOIN Tracks ON
(grproducttrack.grProductID = tracks.GRProductID
AND grproducttrack.grRecordingID = tracks.GRRecordingID)
WHERE
tracks.GRProductID IS NULL
AND tracks.GRRecordingID IS NULL
January 16, 2009 at 1:08 am
As you mention,
For a valid insert the tracks table may contain a record with the same grProductID or the same grRecordingID (as in the source tables), but NOT the same combination of both.
I had thought that the above query was working as described, but I am now finding that some records are not being inserted and it seems to stem from the fact that the destination table has records present that have the same grProductID but different grRecordingID (and vice versa).
As per my uderstanding you can have records for whci grProductID could be same but grRecording is different in Source table. and viceVersa. You query looks like perfect whatever you have written, it 's performing in correct manner. just verify the data from your end.
Abhijit - http://abhijitmore.wordpress.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply