Databases
StackOverflow2013
Postsdbo
POhow to compare 2 tables, remove old records and add new records sql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to compare 2 tables, remove old records and add new records sql
    primarykey
    data
    text
    <p>I have 2 sql tables that are exactly the same format, 1 being used as a temporary table and 1 being used as a static table. </p> <p>Currently my code just wipes the static table and populates it each time with the new data from the temp table, but this isn't exactly what I need. I am trying to create some type of sql diff that compares the 2 tables, that will then delete the records that aren't in the temporary table but are in the static table, and it will add new records that are in the temp table but not the static table. So the static table is only being updated each run instead of being wiped and re-written.</p> <p>So if my temp table has: ABC1, ABC2, ABC4 and my static table has: ABC1, ABC3, ABC4</p> <p>My sql query would ideally return: ABC1, ABC2, ABC4</p> <p>I have 2 queries that seem to select the values I want to remove and the ones I want to add, but I currently can't get the delete one to function properly so I am wondering if there is something I am missing from the query. </p> <p>This query inserts the data that is in the temp table, but not the static table:</p> <pre><code> Insert into [static table] SELECT * FROM [temp table] EXCEPT SELECT * FROM [static table] </code></pre> <p>This query should delete the data that's in static table but not the temp table:</p> <pre><code> delete from [static table] where not exists (SELECT * FROM [static table] EXCEPT SELECT * FROM [temp table] ) </code></pre> <p>Can anyone suggest what the problem is with my query or if there's a better way to perform this task? Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAdaTheDev
    1. USRebeccaD
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat flavour SQL are you using?
      singulars
      1. POhow to compare 2 tables, remove old records and add new records sql
      1. USAdaTheDev
    2. COI'm using sql server 2008
      singulars
      1. POhow to compare 2 tables, remove old records and add new records sql
      1. USRebeccaD
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload