SQL statement

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
LVL 7
ayha1999Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hnasrConnect With a Mentor Commented:
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
 
jerryb30Commented:
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
 
ayha1999Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Gustav BrockCIOCommented:
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
 
Jitendra PatilSr.Software EngineerCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
MERGE statement is what you need...Please read...

http://technet.microsoft.com/en-us/library/bb510625.aspx
0
 
ayha1999Author Commented:
my db MS Access. can I use stored procedure or MERGE or Append in it? Please give a sample if possible.

Thanks
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
Gustav BrockCIOCommented:
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
 
hnasrConnect With a Mentor Commented:
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
 
Gustav BrockCIOCommented:
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
 
ayha1999Author Commented:
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
 
ayha1999Author Commented:
I have new records in gridview and only one table exists. where I have to create table2?
0
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
aikimarkConnect With a Mentor Commented:
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
 
Dale FyeConnect With a Mentor Commented:
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
 
ayha1999Author Commented:
Thanks
0
 
hnasrCommented:
Welcome!
0
All Courses

From novice to tech pro — start learning today.