Pushing data from one data extension to another

Question

In Marketing Cloud, I have two data extensions. Both Data Extensions are being populated via entries in a journey. One runs off of the contact object and the other runs off the opportunity object. The issue is that the journey that runs off of the contact object is unable to bring in the opportunity ID.

I'm looking to pass the opportunity ID from one data extension over to another. I need this field "Contact:Opportunity__r:Id" (from a data extension called "Catalyst pt 1 - 2021-03-26T052629892") to be populated with the value from this field here -> "Opportunity:Id" (populated from a data extension called "New Journey - March 26 2021 1014 AM - 2021-03-26T082359570").

When I run the below I am geting errors. I was hoping to have help with what to do in order to populated one field from a data extension with information from another data extension.

INSERT INTO "Opportunity:Id"
FROM ‘New Journey - March 26 2021 1014 AM - 2021-03-26T082359570’
SELECT "Contact:Opportunity__r:Id"
FROM ‘Catalyst pt 1 - 2021-03-26T052629892’

Answer 1

You would need to find the foreign key(s) that correctly matches each row in the two DEs for this task.

As @MateuszDabrowski stated, INSERT INTO, etc. are not acceptable, only queries via SELECT. To this extent you need to use a JOIN in your SQL Query to accomplish this.

To that extent, you would want something like the following:

SELECT a.primarykey,
b.[Opportunity:Id] as [Contact:Opportunity__r:Id]
FROM [New Journey - March 26 2021 1014 AM - 2021-03-26T082359570] a
INNER JOIN [Catalyst pt 1 - 2021-03-26T052629892] b
ON a.foreignKey = b.foreignKey

/* Target: New Journey - March 26 2021 1014 AM - 2021-03-26T082359570 */
/* Action: Update */

As you did not provide any other information on the data extensions or fields inside, this vague example is the best I can provide.

Your other option is to create an import activity inside of Contact Builder that allows you to copy data from one data extension to another. Inside here you can map the fields correctly without the requirement of doing any SQL.

Answer 2

Marketing Cloud doesn't support INSERT INTO, just standard SELECT.

Use SELECT with JOIN in a Query Activity of the Automation Studio and output to the desired Data Extension.

Answer 3

I needed to do this:

SELECT
cma.Account_Id,
cma.Account_RecordTypeId,
cma.Account_uid__c,
cma.Account_FirstName,
cma.Account_LastName,
cma.Account_Name,
cma.Account_PersonEmail,
cma.Account_email_domain__c,
cma.Account_gender__c,
cma.Account_Valid_Email__c,
cma.Account_Valid_Phone__c,
cma.Account_preferred_language__c,
cma.Account_Has_Logged_Case__c,
cma.Account_gigya_last_modified_date__c,
cma.BannerAccount_first_order_date__c,
cma.Account_Salutation,
cma.Account_global_opt_out__c,
cma.Account_global_opt_out_date__c,
cma.Account_customer_token__c,
cma.Account_social_providers__c,
cma.BannerAccount_Id,
cma.BannerAccount_RecordTypeId,
cma.BannerAccount_customer_status__c,
cma.BannerAccount_banner_code__c,
cma.BannerAccount_last_transaction_date__c,
cma.BannerAccount_total_spend_24_36__c,
cma.BannerAccount_total_spend_12_24__c,
cma.BannerAccount_total_spend_0_12__c,
cma.BannerAccount_total_spend_lifetime__c,
cma.BannerAccount_avg_basket_value_lifetime__c,
cma.BannerAccount_avg_basket_value_24_36__c,
cma.BannerAccount_avg_basket_value_12_24__c,
cma.BannerAccount_avg_basket_value_0_12__c,
cma.BannerAccount_most_recent_order_date__c,
cma.Account_gigya_creation_date__c,
cma.BannerAccount_second_order_date__c,
cma.BannerAccount_third_order_date__c,
cma.BannerAccount_fourth_order_date__c,
cma.BannerAccount_fifth_order_date__c,
cma.BannerAccount_sixth_order_date__c,
cma.BannerAccount_avg_order_freq_0_12__c,
cma.BannerAccount_avg_of_items_in_basket__c,
cma.BannerAccount_implied_consent_flag__c,
cma.BannerAccount_preferred_store_number__c,
cma.Delivery_address_address_id__c,
cma.Delivery_address_address_1__c,
cma.Delivery_address_address_2__c,
cma.Delivery_address_city__c,
cma.Delivery_address_province__c,
cma.Delivery_address_postal_code__c,
cma.Delivery_address_is_primary_address__c,
cma.Delivery_address_last_modified_date__c,
cma.Delivery_address_creation_date__c,
cma.Delivery_address_store__c,
cma.Delivery_address_active__c,
cma.BannerAccount_no_of_completed_orders_lifetime__c,
cma.Account_PersonHasOptedOutOfEmail,
cma.Email_optin,
cma.Email_optout,
cma.CreatedDate,
cma.Delivery_address_Id,
cma.BannerAccount_no_of_completed_orders_24_36__c,
cma.BannerAccount_no_of_completed_orders_12_24__c,
cma.BannerAccount_no_of_completed_orders_0_12__c,
cma.Account_liteAccountCreated,
cma.Account_fullAccountCreated,
cma.BannerAccount_primary_phone_number__c,
cma.Account_OldEmailAddress,
cma.Account_lite_account_source__c,
cma.Acccount_full_account_source__c,
cma.DeliveryType,
cma.ContactEntryTime,
cma.Account_SFSC_airmiles__c,
cma.Account_hasFullAccount__c,
cma.Account_hasliteaccount__c,
cma.Account_postalCode__c,
cma.BannerAccount_digitalReceipts__c,
cma.BannerAccount_phone_number__c,
cma.BannerAccount_promotions__c,
cma.BannerAccount_storeupdates__c,
cma.BannerAccount_survey__c,
cma.Channel__c,
cma.gigya_last_updated_date__c,
cma.Optin_Source,
cma.Customer_Status,
cma.WasLite,
cma.IsAirMilesPresent,
cma.PersonContactId,
cma.BannerAccountUser_Id,
cma.Registered_Mobile_App,
cma.BannerAccount_recipe_Inspiration__c,
cma.SFSC_airmiles_Trimmed,
cma.Global_Suppression_Flag,
cma.Orckestra_Full_Account_Source__c

FROM
Customer_Master_Aggregate_IGA_DE cma
INNER JOIN Journey_EEL_Import imp
ON cma.Account_PersonEmail = imp.email

Then, the problem is that after crossing these two tables, the field Segment, contained in the "Journey_EEL_Import" DE, does not populate in the targeting DE from this SQL: "EEL_CMA_X_EEL" Nevertheless, all the other fields specified here in this Query, do populate.

So what I am trying to do, simplify: Imported a list with two attributes only: email & Segment: Journey_EEL_Import I want to add all the personalization attributes that the main customer table contains, so I cross it with Customer_Master_Aggregate_IGA_DE I also want to ADD the field Segment, contained in the Journey_EEL_Import table, to the Targetting DE: EEL_CMA_X_EEL

I wrote a subsequent SQL to do it, in my case, applying the logic explained here:

SELECT 
a.Segment,
b.PersonContactId, // I had to add this field as it was a Key in my Targetting DE.
a.[Segment] as [Segment_Target]
FROM [Journey_EEL_Import] a
INNER JOIN [EEL_CMA_X_EEL] b
ON a.email = b.Account_PersonEmail

IT WORKED!!