Compare two tables and insert into second one if record is missing.

I have two tables, one of them has the proper numbers and the other one may be missing some of htem, what I want is to have s statement to check if the number exists on the second table and if it doesn't to insert it, to create a new record for it. Please help !! Any help is greatly appreciated.
Table1 is the source
Table2 is the target where the missing fields will be inserted.
Cheers.
ibozcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
1) Please post the schema for both tables.  Be sure to indicate which columns need to be analyzed to see if a given record from Table1 exists in Table2

2) Is this for Access, or for SQL Server?
NerdsOfTechTechnology ScientistCommented:
you will be utilizing a subquery and EXIST or HAVING for your syntax. Please post the schema and the fields you are comparing and updating, etc.
Patrick MatthewsCommented:
>>you will be utilizing a subquery

Why?  A simple outer join will do the trick

>>and EXIST or HAVING for your syntax.

EXISTS, perhaps.  Why HAVING?  there is no indication that a GROUP BY clause is involved...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ibozcAuthor Commented:
The back end is on the SQL server but the query would be executed in Access, let's assume Table1 is the source table with a field called FileNumber and the secondary table called Table2 with the same field name FileNumber. Now those needs to be compared and if table2 does not have a filenumber from table1 a new record should be created in table2 only with  fileNumber inserted, nothing else. Another fields allow for nulls so that should not be a problem. If you need any more info, please let me know. Thank you for your help.
ibozcAuthor Commented:
I forgot to add, I'm looking for SQL syntax that I should use.... thx.
Patrick MatthewsCommented:
INSERT INTO Table2 (fileNumber)
SELECT t1.fileNumber
FROM Table1 t1 LEFT JOIN
    Table2 t2 ON t1.fileNumber = t2.fileNumber
WHERE t2.fileNumber Is Null
Ghosty-BCommented:
or something like this.

INSERT INTO table1
select code from oas_usr_temp
where code not in (select usrname from table2)
NerdsOfTechTechnology ScientistCommented:
thanks @matthewpatrick that looks very good.
Patrick MatthewsCommented:
BTW, the code I posted in http:#a30514983 will do the trick as long as all the other columns allow nulls, and no default value is specified.  If default values are specified, you override them with:

INSERT INTO Table2 (fileNumber, Col2, Col3, Col4)
SELECT t1.fileNumber, Null, Null, Null
FROM Table1 t1 LEFT JOIN
    Table2 t2 ON t1.fileNumber = t2.fileNumber
WHERE t2.fileNumber Is Null

Of course, those other columns in Table2 must still allow nulls...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ibozcAuthor Commented:
Does null mean the same as does not exist? Becuase if the filenumber is not in the table it should be inserted into table2 as a new record with that number from the table1 that is being compared.
ibozcAuthor Commented:
Any one can help? Thx for any help in advance.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Does null mean the same as does not exist?

I presume you refer to this part of matthewspatricks code:

[i]WHERE t2.fileNumber Is Null[/i]

well, this is a side effect "trick" for the LEFT JOIN, if you run it without that condition:
[i]
INSERT INTO Table2 (fileNumber, Col2, Col3, Col4)
SELECT t1.fileNumber, Null, Null, Null
FROM Table1 t1 LEFT JOIN
    Table2 t2 ON t1.fileNumber = t2.fileNumber
[/i]

you would see all records, even those not matching.
with that additional condition, you filter to get only those "not matching", which is what you are interested in.

please try out, and give feedback
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.