Insert new records from another table

[MonkeyHero]

So here's the situation: I have an access database with a standard table, "ID_DATA", and also a linked table, "dbo_STU_D", which is a SQL Server table. Both have a "Primary Key" of sorts, "SID",with unique values. I need a query that can compare the records of the two tables and update the "ID_DATA" table by adding any new records from the other table.

I've tried using many statements, including JOIN, a simple WHERE comparison, and now I'm using a WHERE NOT EXISTS. Here's what I have at this point:

INSERT INTO ID_DATA ( Badge_Number, Birth_Date )
SELECT dbo_STU_D.SID, dbo_STU_D.BIRTH_DATE
FROM dbo_STU_D
WHERE NOT EXISTS (SELECT Badge_Number
FROM ID_DATA);

Here I used the "Birth_Date" column as an example of trying to update code. What I need this to do is to ignore a record if it matches its "Badge_Number" with the "SID" of the linked table (because that would mean that the record already exists). If it finds no match, then it should create a new record with the given information.

When I use this query, it can fetch the data into an empty table, but if there is already data in there, it doesn't update it. For example, I tried deleting a single record and then running it again, and it didn't want to make any changes.

Note that "Badge_Number" and "SID" should have the same exact information, they are just named differently.

Previously, I had just made a query which deleted the data in the table and imported it all over again, but I realized that this is not what I need because there will be some data in the local table that is not included in the SQL table.

So, is there a way to update the data?

 

[PHV]

INSERT INTO ID_DATA ( Badge_Number, Birth_Date )
SELECT dbo_STU_D.SID, dbo_STU_D.BIRTH_DATE
FROM dbo_STU_D LEFT JOIN ID_DATA ON dbo_STU_D.SID = ID_DATA.Badge_Number
WHERE ID_DATA.Badge_Number Is Null


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MonkeyHero]

Thank you very much!
That is exactly what I needed. I had tried doing that, but I was using a "does not equal" comparison between the two ID's in the WHERE clause, which slowed down the query where it wouldn't even process. It works perfectly now.
Thanks again!

P.S. These forums are awesome!