Solved

insert orElse update tsql transaction

Posted on 2010-09-01
15
750 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:JSW21
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
please correct the where clause
to:

WHERE (FirstTable.c1 Is Null)
0
 

Author Comment

by:JSW21
Comment Utility
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
Comment Utility
Thanks
0
 
LVL 4

Expert Comment

by:tabish
Comment Utility
"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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

12 Experts available now in Live!

Get 1:1 Help Now