Solved

Conditional Insert Into Statement

Posted on 2004-10-11
8
694 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

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