I doing something wrong, just I cannot see what it is. I have three columns in one table (tbl_product) I need to insert them into another table if they dont exist already.

scat1, scat2, distributor into columns of the same name in tbl_catergory

INSERT INTO tbl_catergory (scat1,scat2,distributor)
SELECT (scat1,scat2,distributor)
FROM tbl_product
WHERE (SELECT scat1 NOT IN
              (SELECT scat1
                      FROM tbl_product) AND scat2 NOT IN
              (SELECT scat2
                      FROM tbl_product) AND distributor NOT IN
              (SELECT distributor
                      FROM tbl_product)

I know the categories are not in the tbl_catergory, and are in the product table so... i'm doing something wrong.... right?

Again, and again, as always your help is valued

Stuart

hi there..

and your error is???



HTH

Gonzalo

What do you mean by the words them and they in the last sentence above?

Remember that an INSERT statement inserts rows into a table, not columns. You seem to be thinking of things in terms of columns and not rows.

Do you mean you want to insert a row into your tbl_catergory (is that really it's name?) table if the corresponding row does not exist in your tbl_product table?

Why would you want to make duplicates like this? Duplicate data is a no-no. See normalization.

That having been said, I think you'll need something like:
But I have a bad feeling about this. I vaguely recall some issues I had some some time back trying to "see" the contents of a table while I was trying to insert rows into it. The problem was something like the rows being inserted don't come into existence until the INSERT statement has fully completed. Thus, correlating a query against the newly inserted rows won't work because you can't see them until it's too late - until the statement finishes execution.

Try my suggested query to see if it does what you want. If it doesn't, you may have to try something like insert the "missing" rows into a temporary table then load the rows from that.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

You are indeed correct rows not columns, sometimes its really hard to explain what your thinking with these things. The Insert statement works great. Basically this is creating a reference point for products and there future categorisation. I think thats three times you have helped me now, thank you so much for your help.

INSERT INTO tbl_catergory (scat1,scat2,distributor)
SELECT (scat1,scat2,distributor)
FROM tbl_product
WHERE (scat1 NOT IN
              (SELECT scat1
                      FROM tbl_product) AND scat2 NOT IN
              (SELECT scat2
                      FROM tbl_product) AND distributor NOT IN
              (SELECT distributor
                      FROM tbl_product)

Didnt we start with this?