Solved

insert orElse update tsql transaction

Posted on 2010-09-01
15
758 Views
Last Modified: 2012-05-10
What could the best logic if i have some data that need to be update and also insert into a table?
The problem is that data are quite big, it took lots of time. While update data are not that many.
0
Comment
Question by:JSW21
[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
  • 4
  • 4
  • 2
  • +5
15 Comments
 
LVL 16

Expert Comment

by:Stephan
ID: 33583959
I suggest if the insert is really big and the update almost nothing, then make it separate. Not sure what you want to do and how. Maybe you could elaborate
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33583984
Can you please explain a bit more?
Are you doing this through a stored procedure? Are you filling a temp table first?
Is the zone MySQL or MS SQL?
Also, when you say "big", is that in thousands or millions?
0
 
LVL 8

Assisted Solution

by:Mohit Vijay
Mohit Vijay earned 50 total points
ID: 33584003
for insertion use bulk insert option, it will make insertion lots faster, in case of big data, it can be approx 1000 times improvement.

http://msdn.microsoft.com/en-us/library/ms188365.aspx
0
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 6

Expert Comment

by:Jerryuk007
ID: 33584253
Where is the source data stored? Is it a text file, another database, "manual"?

Please provide more details.

Jerry
0
 
LVL 4

Expert Comment

by:tabish
ID: 33584397
Presumably what you are after is the comparison between inserting the data (Refreshing the table delete/insert or truncate/insert etc) and updating the records in a table.

I would say if insert is really big then Updating (if small) is the way to go. Again not sure why would you want to prefer one over other. I mean what your concerns are.
0
 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 150 total points
ID: 33586294
How you would define what suppose to be inserted and what will be updated? And what is means updates are small inserts are big?

It we are talking about row level updates, We usually write "upsert" procedures:

if table has key (column(s) that define unique record) we would do

update myTable set col3=@ool3,...
   where col1=@col1 and col2=@col2
if (@@rowcount=0)
   insert into myTable(col1, col2,col3,...) values(@col1, @col2, @col3,...)

0
 

Expert Comment

by:JamesSizemore
ID: 33590985
Can you elaborate on this? It's not clear from your post just what you're trying to accompllsh. For example, why can't you just do your INSERT and UPDATE statements separately?
0
 

Author Comment

by:JSW21
ID: 33593842
upsert from trofimoval looks good...

sorry,

I am keeping transaction logs with unique is DateTime... the rest are server activities.
the data is moved from another table...

However, i need to match exact DateTime first before can be insert... and update if exist.
 the problem however...
the original table already has like 20k rows... the new table has around few 1000 rows.

the overlap of DateTime is within 10 rows....


0
 

Author Comment

by:JSW21
ID: 33593861
Oh also ...
why can't you just do your INSERT and UPDATE statements separately?

Because i need to check first what is not there in the original table.
and then might have to cut off the update part from another table... to update into it. Before i can insert everything else.

Currently i use similar to upsert but it take too much time.

0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33596442
20K is relatively small table. Make sure you have index on column(s) that you will use in where.
0
 
LVL 4

Accepted Solution

by:
tabish earned 300 total points
ID: 33612447
Please try the following SQL to insert the Records from second table to first. It should be quick enough.

I assumed the both table has the same structures and datatypes etc and may be joined with one col possibaly datetime if it is unique.

This is just to give you an idea how to write it. you can certainly join by coposite keys etc. just change it as per your requirements.

Unfortunately, I'm still unclear on update part of it. not sure what you want to update.

you can write similar query to delete the matching record from the first table using the inner join instead of outer if needs be.

Hope it will help.

INSERT INTO FirstTable( c1, c2,..........cn )
SELECT SecondTable.c1, SecondTable.c2,................cn
FROM SecondTable LEFT JOIN FirstTable ON (SecondTable.c1 = FirstTable.c1)
WHERE (((FirstTable.c1) Is Null)

Open in new window

0
 
LVL 4

Expert Comment

by:tabish
ID: 33612457
please correct the where clause
to:

WHERE (FirstTable.c1 Is Null)
0
 

Author Comment

by:JSW21
ID: 33615032
when you say keys (composite keys) or Index, you mean adding primary key on column?

INSERT INTO FirstTable( c1, c2,..........cn )
SELECT SecondTable.c1, SecondTable.c2,................cn
FROM SecondTable LEFT JOIN FirstTable ON (SecondTable.c1 = FirstTable.c1)
WHERE (((FirstTable.c1 Is Null)

this is very nifty, is it faster than upsert?
0
 

Author Closing Comment

by:JSW21
ID: 33615036
Thanks
0
 
LVL 4

Expert Comment

by:tabish
ID: 33615209
"when you say keys (composite keys) or Index, you mean adding primary key on column? "

I meant any col combination preferably that identifies the unique record but is not necessary and all depends upon the requirements but you can join the tables by any number of columns just like the normal join.

" is it faster than upsert?"

Again depends upon the situation. If you want to update matching records or insert not matching records in bulk from a source (query/table) to a table then it is much faster approach. but if you have to make a decision whether to update/or insert one row at a time then the upsert may the way to go.

Hope it will help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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