0

I am having 4 Data Extension with different number of fields but 1 field that is common in all(EmailField) is my primary key.

I want to update my 5th Target Data Extension with the data that is in other 4.

I want the result exactly same, that we can achieve with Union ALL. But Union all will not work here because it only work when all Data Extension having same number of fields.

Any Solution to get this. It will be better if you can share a code.

1

As your target data extension has to be the one containing all of the fields in Data Extensions 1-4 (that you like to write to it), you can add the additional fields to your select-statements for data extensions 1-4 using a null value and an alias.

Prerequisite: The fields missing in your source data extensions need to be nullable in the target data extension!

SELECT
    Field1,
    Field2,
    NULL as Field3
UNION ALL
SELECT
    Field1,
    NULL as Field2,
    NULL as Field3

In the above example DE1 would be missing Field3 and fill it with a null value in order to be able to use UNION ALL. For DE2 Field2 and Field3 are missing in the example.

If the fields just have different names, you can use aliases and there is no need for the "null hack". This would look like this:

SELECT
    Field1,
    Field2,
    FieldX as Field3
UNION ALL
SELECT
    Field1,
    FieldY as Field2,
    FieldZ as Field3

In the above example "Field3" in your TargetDE would be present in DE1 with the field name "FieldX". In DE2 "Field2" is named "FieldY" and "Field3" is named "FieldZ".

| improve this answer | |
0
SELECT DeOneColumnName1 as DeFiveColumn1, DeOneColumnName2 as DeFiveColumn2, ...,  email
FROM DEOne
UNION ALL
SELECT DeTwoColumnName1 as DeFiveColumn2, DeTwoColumnName2 as DeFiveColumn1, ...,  email
FROM DETwo

Assumption the target dataExtension has only X fields which are named "DeFiveColumn1", "DeFiveColumn2" , ... and email

You can use the as parameter to determine which column should be in which column of the target data source.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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