TEMP TABLE TO REAL TABLE

pallone · June 14th, 2007, 07:18 AM

Hi,

I have some values in a temp table called tblMedia_Master that I want to export to the real table called tblMedia.

These are the rules:

1 - If the VehicleKey (Key) is in both tables then I do an update

2 - If the VehicleKey (Key) is in the temp table(tblMedia_Master ) but not in the Real table (tblMedia) I do an insert.

3 - If the VehicleKey (Key) is in the real table (tblMedia) but not in the temp table(tblMedia_Master ) I need to to a delete.

I am not sure how to add delete statement to my SQL below to achieve item 3 above.

I would appreciate if someone could help me to complete the SQL query below so that I can delete the rows from tblMedia that are not present in the temp table tblMedia_Master

Cheers

SQL

    UPDATE tblMedia
    SET
        VehicleKey = tblMedia_Master.VehicleKey,
        [MediaName] = tblMedia_Master.mediaName,
        Specialty = tblMedia_Master.Specialty,
        FormatType = tblMedia_Master.FormatType,

    FROM tblMedia_Master

    INNER JOIN tblMedia

    ON tblMedia.VehicleKey = tblMedia_Master.VehicleKey

    INSERT INTO tblMedia(
        VehicleKey,
        [MediaName],
        Specialty,
        FormatType,

    )
    SELECT
        VehicleKey,
        [MediaName],
        Specialty,
        FormatType,

    FROM tblMedia_Master
    WHERE VehicleKey NOT IN (
        SELECT VehicleKey FROM tblMedia
    )

joefawcett · June 14th, 2007, 11:02 AM

I would probably use a join rather than a subquery for INSERTs and a similar one for DELETEs.

Code:

INSERT tblMedia(
        VehicleKey, 
        [MediaName],
        Specialty,
        FormatType
    )
    SELECT     
        MM.VehicleKey,
        MM.[MediaName],
        MM.Specialty,
        MM.FormatType,

    FROM tblMedia_Master MM LEFT OUTER JOIN
    tblMedia M ON MM.VehicleKey = M.VehicleKey
    WHERE M.VehicleKey IS NULL;

    DELETE tblMedia
    FROM tblMedia M RIGHT OUTER JOIN tblMedia_Master MM
    ON M.VehicleKey = MM.VehicleKey
    WHERE M.VehicleKey IS NULL

You should look at the query plans for both and do some testing.
Test these surrounded by BEGIN TRAN and ROLLBACK TRAN to make sure they work as expected.

--

Joe (Microsoft MVP - XML)

pallone · June 15th, 2007, 07:50 AM

Hi Joe,

Many thanks for your reply.

I am sorry but I did not undertand you SQL. Could you please elaborate a bit on it specially the LEFT OUTER JOIN and RIGHT OUTER JOIN and IS NULL.

I am a bit confused.

I need to update the real table with the values from the temp table if the vehicleKey is in both but I do not see any update in your query????

Also, If I wanted to use my SQL where would I add the DELETE statement so that if I remove one row from the temp table then it would be deleted from the real table?

Cheers