I need help to update (and insert) data in table1 with newer data from table2.

I am in a very hard situation imagine that i will need to do it for 40 tables in a database of more than 300 tables running in production, if i want to do for all tables, i will just delete the database and restore the dump but this is not my case. I am trying to imagine how i can write a function to do this, using a loop for example, checking if the data is newer(update) or inexistent(insert). So i will need a very generic production or to program a very smart function.

  • So anything the does match you insert? How do you know that no match is and insert? – paparazzo Sep 22 '16 at 20:20
  • A few rows from 2 tables will be enough. So we know what you want to do, which data needs insert and what needs update. – ypercubeᵀᴹ Sep 27 '16 at 17:17
  • Thank you i am at work now, i will provide it when i can. Thanks! May i need to open other question or this can hold for more time? – Luciano Andress Martini Sep 27 '16 at 17:20
  • No need for another question. Edit this one and we can reopen it. (and when answering in comments, you can include the name with an at sign, (e.g. @ypercube) so they (I in this case) get notified of your comment.) – ypercubeᵀᴹ Sep 27 '16 at 17:47

Requirements are not clear on insert. If the ID are not correct then how would you know a non-matching usercode is not already present?

Update: What this does is if t2 is null then it will not replace a value in t1

update t1 
set t1.col1 = insull(t2.col1, t1.col2) .... 
from table1 t1 
join table2 t2 
on t2.usercode = t1.usercode
| improve this answer | |
  • Is that my problem i needed a generic solution, i know it probably does not exist. I know your example. It works for existent lines but does not work for Insert data when lines does not exist on the older table. – Luciano Andress Martini Sep 23 '16 at 12:38
  • No kidding it does not insert. I asked for clarification on insert that you did not provide. – paparazzo Sep 23 '16 at 22:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.