I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this: update myTable set Col1=@col1, Col2=@col2 where ID=@ID if @@rowcount = 0 insert into myTable (Col1, Col2) values (@col1, @col2) My logic behind writing it in this way is that the update will perform an im...
Assume a table structure of MyTable(KEY, datafield1, datafield2...) Often I want to either update an existing record, or insert a new record if it doesn't exist. essentially if (key exists) Run Update command ELSE run insert command What's the best performing way to write this? ...
The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data: if table t has a row exists that has key X: update t set mystuff... where mykey=X else insert into t mystuff... Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this? ...
I need to write a row to the database regardless of whether it already exists or not. Before using NHibernate this was done with a stored procedure. The procedure would attempt an update and if no rows were modified it would fallback to an insert. This worked well because the application doesn't care if the record exists. With NHibernat...
From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed. My code now does a select and if no result is returned it does an insert. I really don't like this code since it exposes me to concurrency issuses when running in a multi-threaded environment. What I would like to do is to put this l...
I need some SQL to update a record in a database if it exists and insert it when it does not, looking around there looks to be several solutions for this, but I don't know what are the correct/ accepted ways to do this. I would ideally like it to work on both Firebird 2 and MySQL 5 as the update will need to be ran against both database...
Hi, i'm creating a procedure to update/insert a table using merge statement(upsert).now i have a problem: using procedure parameters i have to do this upsert. procedure xyz( a in table.a%type,b in table.b%type,....) is some local variables; begin merge into target_table using source_table --instead of the source table, i have to use p...
Hi.. how do i access the procedure parameters inside the same procedure using a query for example: see this procedure procedure game(left in tab.left%type,right in tab.right%type,...) is --some local variables begin merge into tgt_table using subquery --(here is what i need to use the parameters) on some condition when mat...
I have c# project that is using sqlserver compact edition and entity framework for data access. I have the need to insert or update a large amount of rows, 5000+ or more to the db, so if the key exists update the record if not insert it. I can not find a way to do this with compact edition and EF with out horrible performance, ie takin...
Very often, I want to run a query on one of my users where I want a row stored and associated with that user, in a 1-to-1 relationship. So let's say (this is just an arbitrary example), that I have a table that keeps track of a user's car, along with some info about the car. Each user can have either 0 or 1 cars. If the user has no ca...
I'm trying to implement your basic UPSERT functionality, but with a twist: sometimes I don't want to actually update an existing row. Essentially I'm trying to synchronize some data between different repositories, and an Upsert function seemed like the way to go. So based largely on Sam Saffron's answer to this question, as well as som...
Several months ago I learnt from here how to perform multiple updates at once in MySQL using the following syntax INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2); I've now switched over to PostgreSQL and apparently this is not correct. It's re...
I am writing an SSIS package to run on SQL Server 2008. How do you do an UPSERT in SSIS? IF KEY NOT EXISTS INSERT ELSE IF DATA CHANGED UPDATE ENDIF ENDIF ...
Is there an easy way to INSERT an row when not exists, or to UPDATE if it exists, using one MySQL query? ...
Dim sSelect As String = _ "SELECT * FROM Contacts" & _ " WHERE DataSetID = @DataSetID AND ID >= @FirstID AND ID <= @LastID ORDER BY ID" Dim dsDBFiles As New DataSet() Dim cmd As New SqlClient.SqlCommand(sSelect, m_connection) cmd.Parameters.Add("@FirstID", SqlDbType.Int).Value = nFirstID ...
I need to check if a record exists in the table or not from a SELECT statement. If the record exists, do an update otherwise create a record on the table. I'm trying to but i'm getting PLS-00103 error. These are the errors that I'm getting when i run my code in DBVisaulzier: 18:00:09 [DECLARE - 0 row(s), 0.000 secs] [Error Code: 655...
I have mainly been using the Exists Method for merging a row into a table but I am considering switching to the Row Count Method. Is there any reason not to? Exists Method If Exists(Select * From Table Where ID = @ID) Begin Update Table Set Value = @Value Where ID = @ID End Else Begin Insert Into Table (Value) Values (@Va...
Hi there, I'm trying to move some data between two SQL Server 2008 tables. If the record exists in Table2 with the email from Table1 then update that record with the data from Table1, else insert a new record. In Table1 I have a number of columns; firstname, surname, email and so on. I'm not quite sure how to structure the query to up...
I am currently writing an App that needs the ability to modify and persist various pieces of data. I've decided to use Core Data for this purpose. When the user opens the Application for the first time I need to import a large amount of data from a sqlite database, this data consists of the many-to-many relationships. I'd like to find ...
I have an SSIS package set up like this: If I run only the New Rows flow the Bulk Insert finishes without a problem, but as soon as i connect the Live Rows flow the package stalls indefinitely. When I check the activity monitor the Update Newer Table Rows task stalls, blocked by the Insert New Rows task. Why does the Bulk Insert not ...