Solved

insert orElse update tsql transaction

Posted on 2010-09-01
15
755 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

791 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