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

jwcolby jwcolby at colbyconsulting.com
Thu Sep 3 10:22:57 CDT 2009

Thanks Paul.  While I did not use that directly it triggered the duhhh... solution

Which is to create a view that pulls just those records.  Now create an append into the table using 
the view as the select part of the append statement.


Actually I had never created a "not in" query like that in SQL Server but it worked like a champ.

John W. Colby

Paul Hartland wrote:
> 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.
> AS
>  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
> 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

More information about the dba-SQLServer mailing list