Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 14th, 2007, 07:18 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default TEMP TABLE TO REAL TABLE

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
    )
  #2 (permalink)  
Old June 14th, 2007, 11:02 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
  #3 (permalink)  
Old June 15th, 2007, 07:50 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
temp table in query abdusalam Access 5 January 9th, 2008 09:28 AM
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
global temp table vs.permanent table use sofya SQL Server 2000 0 December 17th, 2004 01:57 PM
Using Temp Table and dataset Bhavin Crystal Reports 0 September 14th, 2004 08:39 AM
#temp table object_id hortoristic SQL Server 2000 1 March 22nd, 2004 08:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.