0

I have 8 data extensions that I want to union all into one master data extension. All the column headers are the same for each DE and the master DE. The only differences in the 8 DEs are the records. They are all unique.

My first attempt was to have 8 separate queries that push data into the master DE. That was causing a longer processing time than I wanted. Now I want to run one query that captures all the data in each of the 8 DEs and union all into the master DE. I added part of the query below. The original is pretty long.

UNION ALL

SELECT
LINE_OF_BUSINESS
,LOB_PRODUCT_CODE
,CLIENT_ID
,SOURCE_SYSTEM_LIBRARY
,CLIENT_NAME
,LSTNAM
,FSTNAM
,PS_MEMBER_NUMBER
,PUBLIC_MEMBER_NO
,SSN
,RELCOD
,GENDER
,BTHDAT
,ADDRESS1
,ADDRESS2
,CITY
,STACOD
,POSTAL_CODE
,EFFDAT
,TERM_DATE
,AGE
,GROUP
,BENEPKG
,NETWORK
,MOBILE
,LOCALE
,Email_Address
,COMM_Preferences
,LANGUAGE
,Subscriber_Key
,DSS_Start
,DSS_End
,CONSULT_INCLUDED
,SMS
,CREATED_DATE
,INDUSTRY
,PRODUCT1
,PRODUCT2
,PRODUCT3
,PRODUCT4
,PRODUCT5
,PRODUCT6
,PRODUCT7
,PRODUCT8
,PRODUCT9
,PRODUCT10
FROM NGS_BOB

UNION ALL

SELECT
LINE_OF_BUSINESS
,LOB_PRODUCT_CODE
,CLIENT_ID
,SOURCE_SYSTEM_LIBRARY
,CLIENT_NAME
,LSTNAM
,FSTNAM
,PS_MEMBER_NUMBER
,PUBLIC_MEMBER_NO
,SSN
,RELCOD
,GENDER
,BTHDAT
,ADDRESS1
,ADDRESS2
,CITY
,STACOD
,POSTAL_CODE
,EFFDAT
,TERM_DATE
,AGE
,GROUP
,BENEPKG
,NETWORK
,MOBILE
,LOCALE
,Email_Address
,COMM_Preferences
,LANGUAGE
,Subscriber_Key
,DSS_Start
,DSS_End
,CONSULT_INCLUDED
,SMS
,CREATED_DATE
,INDUSTRY
,PRODUCT1
,PRODUCT2
,PRODUCT3
,PRODUCT4
,PRODUCT5
,PRODUCT6
,PRODUCT7
,PRODUCT8
,PRODUCT9
,PRODUCT10
FROM FMH_BOB

UNION ALL

SELECT
LINE_OF_BUSINESS
,LOB_PRODUCT_CODE
,CLIENT_ID
,SOURCE_SYSTEM_LIBRARY
,CLIENT_NAME
,LSTNAM
,FSTNAM
,PS_MEMBER_NUMBER
,PUBLIC_MEMBER_NO
,SSN
,RELCOD
,GENDER
,BTHDAT
,ADDRESS1
,ADDRESS2
,CITY
,STACOD
,POSTAL_CODE
,EFFDAT
,TERM_DATE
,AGE
,GROUP
,BENEPKG
,NETWORK
,MOBILE
,LOCALE
,Email_Address
,COMM_Preferences
,LANGUAGE
,Subscriber_Key
,DSS_Start
,DSS_End
,CONSULT_INCLUDED
,SMS
,CREATED_DATE
,INDUSTRY
,PRODUCT1
,PRODUCT2
,PRODUCT3
,PRODUCT4
,PRODUCT5
,PRODUCT6
,PRODUCT7
,PRODUCT8
,PRODUCT9
,PRODUCT10
FROM EHC_BOB

UNION ALL
  • Be careful, this may time out depending on how large your DEs are. – Gortonington Aug 23 at 19:10
  • Just take out the union all on the last line – Samuel Dare Aug 28 at 8:53
1

Do it like this:

SELECT LINE_OF_BUSINESS ,LOB_PRODUCT_CODE ,CLIENT_ID ,SOURCE_SYSTEM_LIBRARY ,CLIENT_NAME ,LSTNAM ,FSTNAM ,PS_MEMBER_NUMBER ,PUBLIC_MEMBER_NO ,SSN ,RELCOD ,GENDER ,BTHDAT ,ADDRESS1 ,ADDRESS2 ,CITY ,STACOD ,POSTAL_CODE ,EFFDAT ,TERM_DATE ,AGE ,GROUP ,BENEPKG ,NETWORK ,MOBILE ,LOCALE ,Email_Address ,COMM_Preferences ,LANGUAGE ,Subscriber_Key ,DSS_Start ,DSS_End ,CONSULT_INCLUDED ,SMS ,CREATED_DATE ,INDUSTRY ,PRODUCT1 ,PRODUCT2 ,PRODUCT3 ,PRODUCT4 ,PRODUCT5 ,PRODUCT6 ,PRODUCT7 ,PRODUCT8 ,PRODUCT9 ,PRODUCT10 FROM NGS_BOB

UNION

SELECT LINE_OF_BUSINESS ,LOB_PRODUCT_CODE ,CLIENT_ID ,SOURCE_SYSTEM_LIBRARY ,CLIENT_NAME ,LSTNAM ,FSTNAM ,PS_MEMBER_NUMBER ,PUBLIC_MEMBER_NO ,SSN ,RELCOD ,GENDER ,BTHDAT ,ADDRESS1 ,ADDRESS2 ,CITY ,STACOD ,POSTAL_CODE ,EFFDAT ,TERM_DATE ,AGE ,GROUP ,BENEPKG ,NETWORK ,MOBILE ,LOCALE ,Email_Address ,COMM_Preferences ,LANGUAGE ,Subscriber_Key ,DSS_Start ,DSS_End ,CONSULT_INCLUDED ,SMS ,CREATED_DATE ,INDUSTRY ,PRODUCT1 ,PRODUCT2 ,PRODUCT3 ,PRODUCT4 ,PRODUCT5 ,PRODUCT6 ,PRODUCT7 ,PRODUCT8 ,PRODUCT9 ,PRODUCT10 FROM FMH_BOB

UNION

SELECT LINE_OF_BUSINESS ,LOB_PRODUCT_CODE ,CLIENT_ID ,SOURCE_SYSTEM_LIBRARY ,CLIENT_NAME ,LSTNAM ,FSTNAM ,PS_MEMBER_NUMBER ,PUBLIC_MEMBER_NO ,SSN ,RELCOD ,GENDER ,BTHDAT ,ADDRESS1 ,ADDRESS2 ,CITY ,STACOD ,POSTAL_CODE ,EFFDAT ,TERM_DATE ,AGE ,GROUP ,BENEPKG ,NETWORK ,MOBILE ,LOCALE ,Email_Address ,COMM_Preferences ,LANGUAGE ,Subscriber_Key ,DSS_Start ,DSS_End ,CONSULT_INCLUDED ,SMS ,CREATED_DATE ,INDUSTRY ,PRODUCT1 ,PRODUCT2 ,PRODUCT3 ,PRODUCT4 ,PRODUCT5 ,PRODUCT6 ,PRODUCT7 ,PRODUCT8 ,PRODUCT9 ,PRODUCT10 FROM EHC_BOB
  • The answer provided by CodeRage is correct. I'd just like to add that OP wanted to use UNION ALL, while CodeRage's answer is using UNION. UNION ALL would take all values, UNION would remove any duplicates. In OPs case all records are unique so that wouldn't matter. Note there are also tools like DESelect that provide an easy UI for making SQL queries. – Jonathan Van Driessen Aug 26 at 13:05
  • Thank you all! This helps me significantly! – Kguyton Sep 11 at 18:14

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.