Last post Apr 15, 2016 02:12 PM by limno
Apr 15, 2016 08:05 AM|sdnd2000|LINK
Hi, I am receiving and uploading an excel sheet daily. The data from the sheet could be completely new or updated, so, it could be duplicates in db afterwards.
I am thinking to have a stage table, insert new data to the final table and update existing data with new data. For example, I have col1 as the PK in tables, I am doing the query like the below, I am concerned about the performance. Any better solution.
insert into #test1 (col1, col2, col3)
select col1, col2, col3 from #test2
where #test2.col1 not in (select col1 from #test1)update #test1set col1 = #test2.col1set col2 = #test.col2set col3 = #test.col3from #test2where #test1.col1 = #test2.col1
Apr 15, 2016 11:34 AM|Mikesdotnetting|LINK
I am concerned about the performance
Apr 15, 2016 12:47 PM|sdnd2000|LINK
No issue yet. I am seeking something better if possible.
Apr 15, 2016 01:29 PM|Mikesdotnetting|LINK
I'm not sure you will find anything that's much "better". It's pretty much the way I would approach this task. I might have considered using joins but your way might be a teeny tiny bit quicker. But general rule of thumb - if you don't have any problems,
don't waste time looking for solutions.
Apr 15, 2016 02:12 PM|limno|LINK
Merge #test1 tgt
Using #test2 src on tgt.col1=src.col1
When matched then
set col2 = src.col2,col3 = src.col3
When not matched then
insert (col1, col2, col3) values (src.col1, src.col2, src.col3);
---More samples https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396