[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL statement

Posted on 2013-06-21
18
Medium Priority
?
361 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 52

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 52

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 31

Assisted Solution

by:hnasr
hnasr earned 400 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 52

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 31

Accepted Solution

by:
hnasr earned 400 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 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 46

Assisted Solution

by:aikimark
aikimark earned 200 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
Dale Fye earned 200 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 31

Expert Comment

by:hnasr
ID: 39301553
Welcome!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

650 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