?
Solved

Conditional Insert Into Statement

Posted on 2004-10-11
8
Medium Priority
?
696 Views
Last Modified: 2012-08-13
I need to check if a record exists in a particular table and add it if not: i.e.

INSERT INTO CustomerInfo (CustomerID, FirstName)
VALUES (0,"NoSale"0
WHERE.....

I've tried a couple of Not exisits statements but I can't get it to work. Thanks in advance!
0
Comment
Question by:kevman123
[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
  • 4
  • 4
8 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12280818
To do this, you must select values from one table to insert into another table. Something like:

INSERT INTO CustomerTransactions (CustomerID, FirstName)
SELECT CustomerID, "NoSale" FROM CustomerInfo
WHERE CustomerID NOT IN
(SELECT A.CustomerID FROM CustomerTransactions As A WHERE A.CustomerID=CustomerInfo.CustomerID)
0
 

Author Comment

by:kevman123
ID: 12280890
How do I assign what CustomerTransactions is? It gives an error when I use what you entered.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12280919
The above is just an example. You say you want to see if a record exists in the table - are you looking for a particular customerID in that table? If so, which customerID?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:kevman123
ID: 12280953
Yes, I have a generic CustomerID of 0 that I insert with FirstName = QuickSale. I want to check if the value is there, then add it if it's not. Thanks for your help!
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 12281257
Oh I see. Is the CustomerID the primary key of CustomerInfo? If so, just run the query without any criteria, i.e.:
INSERT INTO CustomerInfo (CustomerID, FirstName)
VALUES (0,"NoSale")

If ID 0 already exists in the table, it simply won't add it (well, it will try but will not succeed since the PK would be duplicated).
0
 

Author Comment

by:kevman123
ID: 12281439
Yes, it is the primary key-- It errors out when I add it again. I would like to know how to conditionally add it through SQL.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12281576
You can't really, not in Access SQL (although you could with SQL Server using a stored procedure). If you are doing it in VBA code, you could use a lookup to see if the value already exists and then run the query if it doesn't.
0
 

Author Comment

by:kevman123
ID: 12286730
Ok thanks for the info!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

801 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