Conditional Insert Into Statement

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!
kevman123Asked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
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
 
shanesuebsahakarnCommented:
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
 
kevman123Author Commented:
How do I assign what CustomerTransactions is? It gives an error when I use what you entered.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
shanesuebsahakarnCommented:
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
 
kevman123Author Commented:
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
 
kevman123Author Commented:
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
 
shanesuebsahakarnCommented:
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
 
kevman123Author Commented:
Ok thanks for the info!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.