Create an automation without a PK

Question

I'm trying to create an automation with a file import to update a data extension.

The problem is that my data extension doesn't have a PK and in order to create a query activity to populate the data extension it requires to have a PK.

Is there a work around for this?

If you need any additional details please let me know.

Thanks

Answer 1

There is no real "solution" for this, as an update in a table will need to know which row to orientate itself on, i.e. requires a PK logically.

But you can of course "deal with it" - The workaround is:

Refrain from having a Data Extension without a PK. Into the DE in question, add another field (e.g. "rowkey"), which you make the PK.

Of course that means that now you have to ensure to enter a unique identifier here whenever data should go into the DE. An obvious tool for this is a GUID - https://en.wikipedia.org/wiki/Universally_unique_identifier

You can use either the AMPScript / SSJS functions GUID() or the SQL function NewId to generate one, see below. You could also use numbering etc, but this will likely turn out more complicated than using GUIDs, which also many external systems will be able to generate.

SQL for generating a GUID:

SELECT NewId() AS rowkey

==

For imports that do not involve either AMPscript / SSJS or SQL - you have to change the import workflow and account for this new mandatory field.

1) Either the originating system comes up with unique IDs and fills the rowkey, or

2) you cannot import directly.

In case 2, you can again use a workaround. Import into a "staging" data extension without PK as usual. Following the import, execute an SQL query that takes all data from staging, adds the rowkey as seen above into the PK field, and targets your "actual" Data Extension.

Result: a data extension like the one you had before, but WITH a PK that does nothing but identify individual rows.

You can then update this DE as desired, making sure to keep the rowkey, but you're free to update everything else.

To be clear: do not create a new GUID ("NewID() as rowkey") when your intention is an "update" SQL query (really this is "add and update"), because this will add new records!

Hope this helps!

Answer 2

For any update operation that you perform, the DE should have a primary key in order to identify the records. If you do not have a primary key then only add & overwrite operations are possible.

Workaround-

1. export the list of records from the DE.
2. Clear all the records within the DE
3. Specify a primary key (Note - The field should act as a unique identifier)
4. Re-import the exported records
5. Then use your import file and query activity to update those records