insert if record exists, else update it?  
Author Message
Obtrunco





PostPosted: Tue May 03 03:57:04 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it?

Hi,

Considering that there are many invocations of sp_INSERT_OR_UPDATE_RECORD,
which is the best routine in performance and stability? (please ignore
trivial syntax errors :) )

Please reply. Thanks in advance.

Regards,
Hyun-jik Bae

------------------------------------

-- Case 1: check whether a duplicated record already exists and inserts or
updates record

begin tran



else

commit tran

-- case 2: insert anyway at first, and then try update record if error
occurs





-- case 3: delete anyway at first, and then insert record

begin tran


commit tran

SQL Server111  
 
 
Uri





PostPosted: Tue May 03 03:57:04 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? Bae

-------Do UPDATE statement
ELSE
-------Do INSERT statement







> Hi,
>
> Considering that there are many invocations of sp_INSERT_OR_UPDATE_RECORD,
> which is the best routine in performance and stability? (please ignore
> trivial syntax errors :) )
>
> Please reply. Thanks in advance.
>
> Regards,
> Hyun-jik Bae
>
> ------------------------------------
>
> -- Case 1: check whether a duplicated record already exists and inserts or
> updates record

> begin tran



> else

> commit tran
>
> -- case 2: insert anyway at first, and then try update record if error
> occurs




>
> -- case 3: delete anyway at first, and then insert record

> begin tran


> commit tran
>
>


 
 
Chandra





PostPosted: Tue May 03 06:22:03 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? Hi
You can write an INSTEAD OF INSERT Trigger.

1. Update the record if there is a duplicate record already existing.
2. Insert the record if no entries exist

This will solve the purpose. U need not maintain a stored Procedure for this.

Trigger will take care of any kinds of inserts (from QA, EM or Application)
that are performed.

thanks and regards
Chandra



> Hi,
>
> Considering that there are many invocations of sp_INSERT_OR_UPDATE_RECORD,
> which is the best routine in performance and stability? (please ignore
> trivial syntax errors :) )
>
> Please reply. Thanks in advance.
>
> Regards,
> Hyun-jik Bae
>
> ------------------------------------
>
> -- Case 1: check whether a duplicated record already exists and inserts or
> updates record

> begin tran



> else

> commit tran
>
> -- case 2: insert anyway at first, and then try update record if error
> occurs




>
> -- case 3: delete anyway at first, and then insert record

> begin tran


> commit tran
>
>
>
 
 
Uri





PostPosted: Tue May 03 06:29:06 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? Chandra
Do you state that to maintain stored procedures is harder than triggers?

Triggers do 'extra work' and you may cause (under some circumstances)
blocking,loking and even deadlocks





> Hi
> You can write an INSTEAD OF INSERT Trigger.
>
> 1. Update the record if there is a duplicate record already existing.
> 2. Insert the record if no entries exist
>
> This will solve the purpose. U need not maintain a stored Procedure for
this.
>
> Trigger will take care of any kinds of inserts (from QA, EM or
Application)
> that are performed.
>
> thanks and regards
> Chandra
>

>
> > Hi,
> >
> > Considering that there are many invocations of
sp_INSERT_OR_UPDATE_RECORD,
> > which is the best routine in performance and stability? (please ignore
> > trivial syntax errors :) )
> >
> > Please reply. Thanks in advance.
> >
> > Regards,
> > Hyun-jik Bae
> >
> > ------------------------------------
> >
> > -- Case 1: check whether a duplicated record already exists and inserts
or
> > updates record

> > begin tran



> > else

> > commit tran
> >
> > -- case 2: insert anyway at first, and then try update record if error
> > occurs




> >
> > -- case 3: delete anyway at first, and then insert record

> > begin tran


> > commit tran
> >
> >
> >


 
 
js





PostPosted: Tue May 03 09:06:13 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? In general, do I need to avoid using triggers?



> Chandra
> Do you state that to maintain stored procedures is harder than triggers?
>
> Triggers do 'extra work' and you may cause (under some circumstances)
> blocking,loking and even deadlocks
>
>
>


>> Hi
>> You can write an INSTEAD OF INSERT Trigger.
>>
>> 1. Update the record if there is a duplicate record already existing.
>> 2. Insert the record if no entries exist
>>
>> This will solve the purpose. U need not maintain a stored Procedure for
> this.
>>
>> Trigger will take care of any kinds of inserts (from QA, EM or
> Application)
>> that are performed.
>>
>> thanks and regards
>> Chandra
>>

>>
>> > Hi,
>> >
>> > Considering that there are many invocations of
> sp_INSERT_OR_UPDATE_RECORD,
>> > which is the best routine in performance and stability? (please ignore
>> > trivial syntax errors :) )
>> >
>> > Please reply. Thanks in advance.
>> >
>> > Regards,
>> > Hyun-jik Bae
>> >
>> > ------------------------------------
>> >
>> > -- Case 1: check whether a duplicated record already exists and inserts
> or
>> > updates record

>> > begin tran



>> > else

>> > commit tran
>> >
>> > -- case 2: insert anyway at first, and then try update record if error
>> > occurs




>> >
>> > -- case 3: delete anyway at first, and then insert record

>> > begin tran


>> > commit tran
>> >
>> >
>> >
>
>


 
 
Bae,Hyun-jik





PostPosted: Tue May 03 09:30:05 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? Thanks for your answer.
BTW, I also experienced some unexpected behavior caused by triggers and I
also think it is not so recommended to write trigger code unless we
understand the behavior precisely.

Regards,
Hyun-jik Bae



> Hi
> You can write an INSTEAD OF INSERT Trigger.
>
> 1. Update the record if there is a duplicate record already existing.
> 2. Insert the record if no entries exist
>
> This will solve the purpose. U need not maintain a stored Procedure for
> this.
>
> Trigger will take care of any kinds of inserts (from QA, EM or
> Application)
> that are performed.
>
> thanks and regards
> Chandra
>

>
>> Hi,
>>
>> Considering that there are many invocations of
>> sp_INSERT_OR_UPDATE_RECORD,
>> which is the best routine in performance and stability? (please ignore
>> trivial syntax errors :) )
>>
>> Please reply. Thanks in advance.
>>
>> Regards,
>> Hyun-jik Bae
>>
>> ------------------------------------
>>
>> -- Case 1: check whether a duplicated record already exists and inserts
>> or
>> updates record

>> begin tran



>> else

>> commit tran
>>
>> -- case 2: insert anyway at first, and then try update record if error
>> occurs




>>
>> -- case 3: delete anyway at first, and then insert record

>> begin tran


>> commit tran
>>
>>
>>


 
 
Uri





PostPosted: Wed May 04 00:27:38 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? js
Use CONSTRAINS instead



> In general, do I need to avoid using triggers?
>


> > Chandra
> > Do you state that to maintain stored procedures is harder than
triggers?
> >
> > Triggers do 'extra work' and you may cause (under some circumstances)
> > blocking,loking and even deadlocks
> >
> >
> >


> >> Hi
> >> You can write an INSTEAD OF INSERT Trigger.
> >>
> >> 1. Update the record if there is a duplicate record already existing.
> >> 2. Insert the record if no entries exist
> >>
> >> This will solve the purpose. U need not maintain a stored Procedure for
> > this.
> >>
> >> Trigger will take care of any kinds of inserts (from QA, EM or
> > Application)
> >> that are performed.
> >>
> >> thanks and regards
> >> Chandra
> >>

> >>
> >> > Hi,
> >> >
> >> > Considering that there are many invocations of
> > sp_INSERT_OR_UPDATE_RECORD,
> >> > which is the best routine in performance and stability? (please
ignore
> >> > trivial syntax errors :) )
> >> >
> >> > Please reply. Thanks in advance.
> >> >
> >> > Regards,
> >> > Hyun-jik Bae
> >> >
> >> > ------------------------------------
> >> >
> >> > -- Case 1: check whether a duplicated record already exists and
inserts
> > or
> >> > updates record

> >> > begin tran



> >> > else

> >> > commit tran
> >> >
> >> > -- case 2: insert anyway at first, and then try update record if
error
> >> > occurs




> >> >
> >> > -- case 3: delete anyway at first, and then insert record

> >> > begin tran


> >> > commit tran
> >> >
> >> >
> >> >
> >
> >
>
>


 
 
--CELKO--





PostPosted: Wed May 04 10:04:26 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? There is a statement for this operation in SQL-99 called MERGE, which
other products have implemented. For example in DB2:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0411simchuk/

Look at the syntax and see what the options are so you can plan your
procedure to match the standards. Here is one solution which assumes
that you have a table of rows for the merge. It avoids procedural
code, but needs some error handling.

BEGIN
INSERT INTO Foobar (keycol, col1)
SELECT M.keycol, col1
FROM MergeTable AS M
WHERE NOT EXISTS
(SELECT *
FROM Foobar
WHERE Foobar.keycol = Merge_table.keycol);
UPDATE Foobar
SET col1 = (SELECT col1
FROM MergeTable
WHERE Foobar.keycol = MergeTable.keycol)
WHERE EXISTS
(SELECT *
FROM Foobar
WHERE Foobar.keycol = Merge_table.keycol);
END;

 
 
js





PostPosted: Wed May 04 11:05:13 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? That's cool...



> There is a statement for this operation in SQL-99 called MERGE, which
> other products have implemented. For example in DB2:
> http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0411simchuk/
>


 
 
J





PostPosted: Wed May 04 20:02:48 CDT 2005 Top

SQL Server Developer >> insert if record exists, else update it? Am I missing something or will all the rows inserted in the first statement
also be updated in the second? May be reverse the order and UPDATE first
and then INSERT?

>
> BEGIN
> INSERT INTO Foobar (keycol, col1)
> SELECT M.keycol, col1
> FROM MergeTable AS M
> WHERE NOT EXISTS
> (SELECT *
> FROM Foobar
> WHERE Foobar.keycol = Merge_table.keycol);
> UPDATE Foobar
> SET col1 = (SELECT col1
> FROM MergeTable
> WHERE Foobar.keycol = MergeTable.keycol)
> WHERE EXISTS
> (SELECT *
> FROM Foobar
> WHERE Foobar.keycol = Merge_table.keycol);
> END;
>

Joe De Moor

PS. And I assume you mean MergeTable and not Merge_table in the WHERE
clauses...heheh