Source Tables have dynamic names how can we process them?

[jonna]

Hi Replicate friends

The source system we are collecting data from stores its records into a NEW TABLE EVERY DAY - this is because of the sheer volumes being processed. The tables are named as follows: "travel_data_yyyymmdd" with one for every day. 

Is there a way to create a single task that can read these table names dynamically?  Perhaps something along the lines of using wildcards??

Thanks in advance

ianjonna

[lyka · Accepted solution]

Hello,

You can use the "Include" option under Table Selection.

Example

Source Tables 

Schemas: Table:

Table Selection Patterns

Pattern	Load Order
Include dbo.travel_data_%

Thanks

Lyka

[jonna]

Lyka, that is awesome, thanks very much for your help 😁

[Heinvandenheuvel]

@jonna  In a recreate of the same topic with no further details you indicate the solution thus far did not tick all the boxes.

My recommendation is to delete that new topic, continue with a reply here explaining in detail why the solution was insufficient. Remember, we are not mind-readers. Help us help you.

Note 1: The indicated solution "travel_data_%" is slightly misleading suggesting that the match is for "travel" followed by a widcard  character followed by "data" followed by underscore followed by anything. It matches "travel_data_yyyymmdd" but also "travelXdataXyyyymmdd"

The underscore character is a single character wildcard and thus the solution provided is almost equivalent to "travel%data%". If the underscore is required, then you must provide it as a single character in a class: [_]

Note 2: Many application with source tables find that it seems a good idea at a time, but it really sucks! It may make some sense on a resource constraint OLTP source, but on the target (BI system) a single table holding 'travel_data' for all dates may well be preferable. If so, create a GLOBAL TRANSFORMATION to remove the '_yyyymmdd' from the source name to generate a target. Unfortunately the source table name is NOT available as a variable, so you cannot add a computed column to indicate the origin in the combined target table. Still, there is a likely a date column already to satisfy that need.

Note 3: You may find that while global replace is neat, when all is said and done you are better of just generating the JSON for 100+ anticipated tables, and add a helper column with origin if needed. This is spcially true if changes to the dated source tables are limited in date scope like only allowed within 30 days of creation. Generate a fresh task every 30 days for 30 days backwards, and 70 days future. Stop and resume.

Good luck!

Hein

[jonna]

Good afternoon Hein

Very grateful for your help on this one, it seems you have hit the nail right on the head in relation to the specific help I was looking for.  I will do some testing right now and come back to update my entry in here.

Many thanks (again)

ianjonna