[dba-SQLServer] Append only records where some fields not in the table already

John,

Not had time to test this as just leaving work, but don't think it will be
too far out, then repeat for tables 3,4,5.

CREATE PROCEDURE MergeTables
AS
BEGIN
 INSERT INTO Table1 (Name, Address, Zip5, Zip4, Age)
 SELECT Name, Address, Zip5, Zip4, Age
 FROM table2 RIGHT OUTER JOIN Table1 ON Table2.Name=Table1.Name AND
Table2.Address=Table1.Address AND Table2.Zip5=Table1.Zip5 AND
Table2.Zip4=Table1.Zip4
 WHERE Table1.Name IS NULL AND Table1.Address IS NULL AND Table1.Zip5 IS
NULL AND Table1.Zip4 IS NULL
END

2009/9/3 Gustav Brock <Gustav at cactus.dk>

> Hi John (I have posted this in 2000, 2001, and 2006 - even as a reply to a
> posting of yours)
>
> This tip from Smart Access is one of my favourites:
>
> <quote>
>
> Update and Append Records with One Query
>
> By Alan Biggs
>
> Did you know that you can use an update query in Access to both update and
> add records at the same time? This is useful if you have two versions of a
> table, tblOld and tblNew, and you want to integrate the changes from tblNew
> into tblOld.
>
> Follow these steps:
>
> 1. Create an update query and add the two tables. Join the two tables by
> dragging the key field of tblNew onto the matching field of tblOld.
>
> 2. Double-click on the relationship and choose the join option that
> includes all records from tblNew and only those that match from tblOld.
>
> 3. Select all the fields from tblOld and drag them onto the QBE grid.
>
> 4. For each field, in the Update To cell type in tblNew.FieldName, where
> FieldName matches the field name of tblOld.
>
> 5. Select Query Properties from the View menu and change Unique Records to
> False. (This switches off the DISTINCTROW option in the SQL view. If you
> leave this on you'll get only one blank record in your results, but you want
> one blank record for each new record to be added to tblOld.)
>
> 6. Run the query and you'll see the changes to tblNew are now in tblOld.
>
> This will only add records to tblOld that have been added to tblNew.
> Records in tblOld that aren't present in tblNew will still remain in
> tblOld.
>
> </quote>
>
> I guess it works in SQL Server as well.
>
> /gustav
>
>
> >>> jwcolby at colbyconsulting.com 03-09-2009 16:32 >>>
> I have a set of 5 tables.  These tables contain names and number of
> children in age ranges.  As a
> first pass at processing, I need to merge the 5 tables into one table such
> that the name / address
> only gets into the table one time, IOW every record in the first table, but
> only the records in
> table 2 where the name / address data is not already in the destination
> table.
>
> SQL Server 2005.  Clustered index on the name / address / zip5 / zip4 of
> the source tables.
>
> I have not been able to find an append syntax that will append the records
> not in the table but
> prevent appending those already in, without aborting the entire append
> operation.
>
> What is the magic key here?
>
> --
> John W. Colby
> www.ColbyConsulting.com <http://www.colbyconsulting.com/>
>
>
>
>  _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com