Solved

SQL statement

Posted on 2013-06-21
18
359 Views
Last Modified: 2013-07-05
Hi,

How can I write statement in MS access to insert a record if not record exists and update record if the record exists in a single statement.

Suppose two records are there in a gridview control

Id   Name
1     abc
2     xyz

and in the table
Id  Name
2   xyz

when I insert/update records into the table, the first record id=1 need to insert and id=2 need to update because it already exists.

Please help.

ayha
0
Comment
Question by:ayha1999
[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
  • 5
  • 4
  • 3
  • +5
18 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39267493
I doubt you can do that in a SQL statement, because the syntax is so different. You might be able to code the check, and execute one statement or another.
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267554
can I do it with IIF statment? I tried in the code but not working. I tried both insert and update in single and multiple loops but not working. It was possible in sql server with IF Exists statment. How can I do it in the code?

Thanks
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39267608
You can do that. Usually one table is appending/updating another, so you may have to adopt a little to fit your gridview:

Combined "update or Append" query

/gustav
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 13

Expert Comment

by:jitendra patil
ID: 39267615
you can do it using stored procedures and inside stored procedure
query for id and name using if exists statement.

CREATE PROCEDURE ProceduerName
@yourparameterlist

AS

IF Not Exists(Select Name from tablename where Id=@ID)
 BEGIN
  //Insert statement
 END
ELSE
 BEGIN
   //Update Statement
  END      

hope this helps.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39267633
MERGE statement is what you need...Please read...

http://technet.microsoft.com/en-us/library/bb510625.aspx
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267638
my db MS Access. can I use stored procedure or MERGE or Append in it? Please give a sample if possible.

Thanks
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39267643
Ooops...I did not realize this is Access since it was posted in the SQL Server section.  Sorry but MERGE can not be used in Access...My guess would be that you need to use VB Access to branch out the code.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39267654
You can do it with VB(A) as well - a loop and using FindFirst and then either Edit or AddNew.
But SQL (as shown) is much faster in this case.

/gustav
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
ID: 39267683
If id in table1 exists in table2 update, otherwise insert in table2

You may try 2 queries:

1. delete table2.* from table2 INNER JOIN table1 on table2.id=table1.id;
2. Insert into table2 select * from table1;
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39267685
You would only have to use two queries if no IDs existed and you would have to fall back to a field by field comparison.

If you have the IDs, one query will do.

/gustav
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267690
I have already tried the following code but only one query works. I also tried both insert and update in the loop but didn't work.

insertQuery(insert into table...
Loop 1:
 for (int j = 0; j < gvFiles.Rows.Count; j++)
        {
          insertQuery

...
}

UpdateQuery(Update table...
Loop 2:
 for (int j = 0; j < gvFiles.Rows.Count; j++)
        {
          UpdateQuery

...
}
0
 
LVL 30

Accepted Solution

by:
hnasr earned 100 total points
ID: 39267701
Any feedback on trying the delete then the insert queries?

The idea, if in table1 you have 1, x and 2, y records.
And in table2 you have 1,x record
then delete deletes 1,x from table 2
and insert inserts the new (1,x) as if updated and the other 2,y record.
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39267704
I have new records in gridview and only one table exists. where I have to create table2?
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 total points
ID: 39267705
Calling a Query for each row inside a loop is extremely slow.

If you don't want to use straight SQL (which I wouldn't) then use a DataTableAdapter to open the table and you have an object you can search, edit, and append to very fast.

/gustav
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 50 total points
ID: 39267826
create a unique index on your destination table and it will prevent duplicates.  You could then run an append query and only the new/differentID rows would be inserted.  If you are iterating through the source recordset, inserting each record into the destination table, you would trap for a duplicate key exception and keep iterating.
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39267852
You can do it with an UPDATE query if both sets of data are in a table or a query result set, but not sure how (other than VBA) to do it with a single query and a data grid.  Assume you have:

Table1
Id   Name
1     abc
2     xyz

and Table2
Id  Name
2   xyz

Then you could write:

UPDATE Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
SET Table2.Name = Table1.Name

IN Access, this works, but I have not found any other relational database that will do this in a single UPDATE query.  This will update records that already exist in Table2 and insert any new records that are in Table1 but not in Table2.  You could replace Table1 with a query if you need to.
0
 
LVL 7

Author Closing Comment

by:ayha1999
ID: 39300593
Thanks
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39301553
Welcome!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

688 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