Reorganizing order of fields in data extension

Question

I see your answer on rearranging order of fields in a Data Extension through SQL query here Reorganizing fields in data extension

May I ask you how to rearrange order of columns via SQL query to have the correct field layout order?

That would be super helpful since I deal with around 10 Data Extensions with more than 100 columns each and need to rearrange the order of fields within them

Answer 1

To quote how it was explained in the post:

... utilize a SQL query to create a new DE with the correct field layout.

If your 10 Dataextension with 100 columns have the same fields you would do it like this.

• Create a DataExtension with all those 100 fields in the correct order. I call this dataextension DEX_CORRECT (you would name it different).
• Copy this dataextension 9 times because you had 10 dataextension with these 100 fields (assumption of mine).
• Then you create an SQL query like this SELECT * FROM [sourcedex]
• The target dataextension is your new dataextension (for this every column has to be exactly named like the one in the source dataextension). (10 different SQL queries / activities)

• If you want to rename some columns you have to do this example in the source the column is named firstname and in the target its called first_name

  SELECT source.firstname as first_name, source.nextcolumn as idontknow, source.notrenamed, source.notrenamed2, ... FROM [sourcedex] as source

• And this with every field you renamed. For those you did not rename you can just write source.columnname without an as.

That would be the way how to rearranged this. Be aware that this you have to adapt every automation that has been attached to the dataextensions you had before and change it to the new one.

Just by the way: A table with 100 columns seems to be not a good table layout (this table can get big very very quick - which can be a performance killer)