MERGE Query

reli

Hi all,
I use this query to marge two tables:

MERGE into tst.tableD  D using tst.tableA A 
ON A.id=D.id AND A.c1_ = D.c1_ --- CONSTRAINT UC_c1 UNIQUE (id,c1_) 
WHEN MATCHED THEN UPDATE SET c2_=A.c2_ ,c3_=A.c3_ 
WHEN NOT MATCHED THEN INSERT (id, c1_, c2_, c3_, C4) VALUES ( A.id, A.c1_, A.c2_, A.c3_, A.C4)

my questions are:
1.I need to marge data from CSV files instead of using table A,I load data to temp table (table A) and then do the
merge query. there is a better solution without the temp table?

2.in table A and table D the is lot of column more then 290 and in the same name ,ther is a better slounig
in the update part instead : SET c2_=A.c2_ ,c3_=A.c3_ ........c289=A.c298,c290=A.c290?

Thank you all!!

dave_wood

Hello,

You could try making an external table for your csv file, see the documentation here

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/CreatingExternalTables.htm?tocpath=Administrator's Guide|Working with External Data|_____2

You could then just copy the csv file you want to merge to the location that’s specified rather than actually importing it into the database. It might be simpler just to do the import though because if you’re not the owner of the database there’s quite a lot of setup involved to make the file location and to get access to it so you can copy your csv files.

When doing the UPDATE you should make a predicate so that you don’t systematically update every column when you get a match and you change only the ones that are different

WHEN MATCHED THEN UPDATE SET c2_=A.c2_ ,c3_=A.c3_
WHERE c2_<>A.c2_ OR c3_=A.c3_ OR …

When doing the insert you can just do

WHEN NOT MATCHED THEN INSERT VALUES (A.*)

If there’s a lot of data use the DIRECT hint

MERGE /+DIRECT/

Regards
Dave