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. Duuuhhh... Actually I had never created a "not in" query like that in SQL Server but it worked like a champ. John W. Colby www.ColbyConsulting.com 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. > > 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 >> >> > >