CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Happy
Happy

Reputation: 323

How to create a stored procedure

I have two tables, table1 and table2

table1 columns

product_name 
product_info

table2 columns

product_name 
product_info 
Product_address 
etc

I need to insert into table1 if any new product name is inserted in table2

I tried below code but its not inserting. Where did I made a mistake?

as
begin
    insert into  table2  (product_name product_info Product_address etc) values   (...);

    INSERT INTO table1(Product_Name)
         SELECT Product_Name 
         FROM tb_new_purchase
         WHERE NOT EXISTS (SELECT Product_Name FROM tb_new_product_Name_id )
 end

Upvotes: 1

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You need a correlated subquery. I find your table names inconsistent. I think this is what you want:

INSERT INTO table1(Product_Name)
     SELECT Product_Name 
     FROM table2
     WHERE NOT EXISTS (SELECT Product_Name
                       FROM table1
                       where table1.Product_Name = Table2.Product_Name
                      );

Your query never inserts a new name, because there is at least one row in the query. By the way, you can also express this as:

INSERT INTO table1(Product_Name)
     SELECT Product_Name 
     FROM table2
     WHERE Product_Name not in (SELECT Product_Name
                                FROM table1
                               );

Upvotes: 1

Paul Michaels
Paul Michaels

Reputation: 16685

It would appear that it will only insert where tb_new_product_name_id is empty.

Upvotes: 0

Related Questions