?
Solved

Conditional Insert Into Statement

Posted on 2004-10-11
8
Medium Priority
?
706 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
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 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