Solved

insert orElse update tsql transaction

Posted on 2010-09-01
15
752 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
  • 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
 
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
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.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now