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’

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.


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.

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.