Solved

insert a record into a table where there IS a record in another

Posted on 2011-03-22
7
324 Views
Last Modified: 2012-05-11
I have two tables:

Products & Product_Categories

I want to INSERT a record into Product_Categories if there is not already one for that product_ID in the Product_categories table for each product_ID in the Products table.

Product_Categories field should have the product_ID and a value of 0 for Category_ID



0
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 35190091
INSERT INTO Product_Categories(whatever fields are in this table) 
SELECT whatever fields you need for Product_Categories 
FROM Products p LEFT OUTER JOIN Product_Categories pc ON (p.productId = pc.productId) 
WHERE some_field_from_ProductCategories IS NULL

Open in new window

0
 
LVL 7

Expert Comment

by:TheTull
ID: 35190093
Have you tried adding a where clause like this:

where product_id not in (select distinct product_Id from product_categories)

0
 
LVL 4

Accepted Solution

by:
jimtpowers earned 500 total points
ID: 35190097
I have two tables:

Products & Product_Categories

I want to INSERT a record into Product_Categories if there is not already one for that product_ID in the Product_categories table for each product_ID in the Products table.

Product_Categories field should have the product_ID and a value of 0 for Category_ID

INSERT INTO Product_Categories (Category_ID, Product_ID)
SELECT '0',
p.Product_ID
FROM Products AS p
WHERE (p.Product_ID NOT IN (SELECT DISTINCT Product_ID FROM Product_Categories))
0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 9

Expert Comment

by:Roman Gherman
ID: 35190098
Hi,
Try this

INSERT INTO Product_Categories
(col1, col2)
SELECT
 col1,
 col2
FROM Products
WHERE
product_ID  NOT IN (SELECT product_ID FROM Product_Categories )
0
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 35190130
Depending on the number of records you expect to be returned from Products, NOT IN is a really inefficient way to do that. At the very least you should use NOT EXISTS. The most efficient way is with a LEFT OUTER JOIN and then checking for NULL in the WHERE.
0
 
LVL 9

Expert Comment

by:gery128
ID: 35190144
you can use ADO.NET command with ExecuteScalar method of command object to check if the returning Category_ID is 0 or something other and if not then use another query with ExecuteNonQuery to insert the record.

like this:
cmd.CommandText = "select Category_ID from Product_Categories where Product_ID="@prodId";
int catId = (int)cmd.ExecuteScalar();
if(catId==0)
cmd.CommandText =@insert into Product_catergories...";
cmd.ExecuteNonQuery();


0
 

Author Closing Comment

by:vbnetcoder
ID: 35190396
THANK YOU everbody but this is the solution I used
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question