Comparing two tables and appending data to destination table 
Author Message
 Comparing two tables and appending data to destination table
I have two identical tables where I would like to compare rows in the source
table with rows in the destination table and append any new rows that don't
exist in the destination table to the destination table.

Is this possible??

TIA



Mon, 25 Aug 2003 22:49:09 GMT
 Comparing two tables and appending data to destination table

Try this:

insert into destination_tbl
select * from source_tbl where id_field not in (select id_field from
destination_tbl)

Quote:

> I have two identical tables where I would like to compare rows in the source
> table with rows in the destination table and append any new rows that don't
> exist in the destination table to the destination table.

> Is this possible??

> TIA



Mon, 25 Aug 2003 23:31:32 GMT
 Comparing two tables and appending data to destination table
Ammie,

Any pimary/Unique keys for the two tables??It would be useful if you can
pass the DDL of the tables??

Dinesh.



Mon, 25 Aug 2003 23:43:59 GMT
 Comparing two tables and appending data to destination table
Ammie,

Is there a particular field in the source table you can check the destination
table for?  If so, there are a couple ways to write it.  You can either use a
subquery or a left join to return any values in the source table that do not
exist in the destination table.  This example uses the Pubs database and returns
all publishers who are not in the Titles table.  If this works for you, it
should give you the select list for your insert.

SELECT * FROM Publishers WHERE Pub_Id NOT IN(SELECT Pub_Id FROM Titles)

SELECT * FROM Publishers P LEFT JOIN Titles T  ON P.Pub_Id = T.Pub_Id
 WHERE T.Pub_Id IS NULL

Quote:

> I have two identical tables where I would like to compare rows in the source
> table with rows in the destination table and append any new rows that don't
> exist in the destination table to the destination table.

> Is this possible??

> TIA



Mon, 25 Aug 2003 23:57:56 GMT
 Comparing two tables and appending data to destination table
Ammie,

Quote:
>I have two identical tables where I would like to compare rows in the source
>table with rows in the destination table and append any new rows that don't
>exist in the destination table to the destination table.

A third alternative, and the one I prefer to the LEFT JOIN with NULL
test or the NOT IN test:

INSERT INTO Destination
SELECT *
  FROM Source as S
 WHERE NOT EXISTS(select * from Destination as D
                   where S.pk = D.pk)

If your criteria for insertion are more complicated than the simple
non-existence of the key you will have to deal with that too.

Roy



Tue, 26 Aug 2003 02:09:15 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. DTS: Using table from Source and table from Destination to create new table in Destination

2. Appending data with fewer columns than destination table

3. SQL - Join one table in one destination to another table in another destination (DBF)

4. Problem with appending one table to two tables - referential integrity - help please

5. APPENDING from two tables to a third table

6. Comparing table contents of two tables

7. Query to compare the data in two tables?

8. DAO code to compare two fields in two tables

9. comparing data in two different tables

10. Compare data in two tables help Please ??

11. Comparing two columns in two tables

12. Retrieve data from two tables, from two databases, in two registered servers


 
Powered by phpBB® Forum Software