Another solution to avoid all the lookups is to use a table input and load the source table. Then you can use "Merge Rows (diff)" to do a comparison between the old set and the new set. It will mark the rows as new,identical,changed,deleted after that you can use "Synchronize after merge" and it will do the insert or update depending on the flag field.
This way you do not bash your database with all the lookup queries, you do have to load and sort the entire source table to do the comparison against. Cheers, Hans On Tue, 30 Aug 2022 at 13:47, <[email protected]> wrote: > > Thank you both for quick reaction. > > I'm searching for some way to quickly update table with millions of > records. > > I know 'Insert / update' but this is terrible slow - so may queries. > > I know I can do some workaround like instert into temp table and execute > SQL query (update table taking data from another table). > I was thinking that dimension table is something that could work here. > > > > *Sent:* Tuesday, August 30, 2022 at 1:09 PM > *From:* "Bart Maertens" <[email protected]> > *To:* [email protected] > *Subject:* Re: Dimension lookup/update > Hi Mike, > > The Dimension Lookup/update transform populates Slowly Changing > Dimensions, typically type 2 [1] with some additional functionality. > > The "date range start", "date range end" and "version" fields are > technical fields that are used to determine the validity period and version > number for a dimension record. > If you don't need any of the versioning information, you're probably not > maintaining a slowly changing dimension. > Hop comes with other transforms like "Table output", "Insert/update", > "Database lookup" etc that are better suited to maintain regular tables or > type 1 dimensions. > > [1] > https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row > > Regards, > Bart > > On Tue, Aug 30, 2022 at 1:01 PM <[email protected]> wrote: > >> >> Hello! >> >> I try to use 'Dimension lookup/update' hop and I do not understand the >> meaning of two fields in properties: >> >> "Date range start field / Table date range end" >> >> My data has no date column. I do not need it. So I cannot specify any >> date. What this field is for and how to ignore it? >> >> >> What is exactly 'Version field'? This is to keep several datasets in one >> table? I.e same record can have multiple copies with different 'Version'? >> What if I do no not need (most of scenarios I think) any 'versions', I >> just need single copy of data? >> If I have 'version number' I can leave 'key fields' empty? >> >> Regards, >> >> Mike >> >> >> >> > > > >
