Click here to Skip to main content
15,077,641 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
TABLE A   TABLE B
SLNO      SLNO
NAME      NAME

TABLE A RECORDS LIKE
1 THOMAS

TABLEB RECORDS LIKE
1 JOHN
2 THOMAS
3 JOSEPH
4 RAHULSir


I have two table ( TABLEA & TABLEB )
How i can transfer TableB to TableA ( duplicate entry avoid )


MY DATABASE MS ACCESS AND VB.NET

What I have tried:

INERT INTO TABLEA(SLNO) SELECT SLNO FROM TABLEB
Posted
Updated 4-Sep-21 5:19am
v2

First select just the records you need to INSERT: because Access doesn't support the SQL EXCEPT clause, you need to use a LEFT JOIN:
SELECT A.x FROM A 
LEFT JOIN B 
ON A.x = B.x 
WHERE B.x IS NULL

Then you can just INSERT those into your TableA

Get the SELECT working first in a straight Access query, then add the INSERT to your VB code.
   
You'll need to use a correlated 'Where Not Exists' clause to insert the data into [Table A] that doesn't exist in [Table B]. You'll also need to define a key to each table for the insert to work.

This should be pretty close to what you need for the insert statement.

SQL
INSERT INTO Table1 ( SLNO, Name )
SELECT t2.SLNO, t2.Name
FROM Table2 AS t2
WHERE (((Not Exists (SELECT 1
                      FROM Table1 as t1
                      WHERE t2.[SLNO] = t1.[SLNO] AND
                            t2.[Name] = t1.[Name]))=False));
   
Comments
Member 13322574 5-Sep-21 22:08pm
   
Thanks...code is working proper....have a great support from code project

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900