SQL Query Question

[bakedjake]

I have two tables, master table and insert table. Identical table structure.

The master table has a long list of data.

The insert table has some data I've collected that I want to insert into the master table.

Is there a way I can easily insert only the items from the insert table into the master table which don't exist. In other words, I don't want to duplicate any information already in the master table.

I'd prefer to do this only in SQL, unless there's a performance reason to do it in code (I don't think there would be).

[GaryK]

I don't know if you're using MySQL or SQL Server. In SQL Server there is an EXISTS clause. So what I do is:

IF NOT EXISTS(query master table for item in insert table)
INSERT INTO ...

[bakedjake]

Thanks Gary, that's actually why I'm asking. I know EXISTS is in SQL Server, but doing this on MySQL.

I miss my SQL Server box. ;-)

[GaryK]

I'm sorry I couldn't help you out. I'm sure someone will have a solution for you soon.

EDIT: Is there a way to test for NULL in MySQL? Maybe you could test for some value in the master table being NULL and if it is then do your INSERT?

[coopster]

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, so yes, you could use

EXISTS and NOT EXISTS

clauses at that level.

Prior to that version you could use an INSERT ... SELECT [dev.mysql.com] if you have

PRIMARY KEYS

defined.