Solved

Conditional Insert Into Statement

Posted on 2004-10-11
8
688 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now