?
Solved

insert orElse update tsql transaction

Posted on 2010-09-01
15
Medium Priority
?
760 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 200 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 600 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 1200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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