query to copy dataset with modification | Community
Skip to main content
DavidSlaw1
DavidSlaw1
Level 3
July 18, 2025
Solved

query to copy dataset with modification

  • July 18, 2025
  • 2 replies
  • 337 views

Data in legacy dataset is source from Adobe Analytics (lots of columns)

I need to modify one column in the dataset and write out the record to a new dataset

I do not want to list 1,000+ columns in my select or insert queries

I known I can use "with (schema=<schema>)" when creating the output dataset

What I want is the select query to also reference the schema 

 

create table with (schema=<schema1>) as 

select <something here to get all columns from the dataset>, <somefunction>(offending column) as original column

From <dataset>

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by supratim320

Hi @davidslaw1 ,

If I understand it correctly: you are attempting the whole dataset creation and insert records into the new dataset all in a single go.

In that case: I will do it in 2 steps:

  1. Create dataset based on the schema: so I know the dataset/ table name beforehand
  2. In query service, I will write an insert statement:

Insert into my_dataset (

SELECT COL1, COL2,.... FROM <legacy dataset>

)

Since, my schema remains same in both cases, I do not need to worry about destination & source field mapping, it will be the same. And in the same query, you can manage your new column.

 

I hope this is what you were looking for!

2 replies

S
supratim320Accepted solution
Level 5
July 25, 2025

Hi @davidslaw1 ,

If I understand it correctly: you are attempting the whole dataset creation and insert records into the new dataset all in a single go.

In that case: I will do it in 2 steps:

  1. Create dataset based on the schema: so I know the dataset/ table name beforehand
  2. In query service, I will write an insert statement:

Insert into my_dataset (

SELECT COL1, COL2,.... FROM <legacy dataset>

)

Since, my schema remains same in both cases, I do not need to worry about destination & source field mapping, it will be the same. And in the same query, you can manage your new column.

 

I hope this is what you were looking for!

    Sukrity_Wadhwa
    Community Manager
    Sukrity_WadhwaCommunity Manager
    Community Manager
    July 29, 2025

    Hi @davidslaw1,

    Was the given solution helpful to resolve your query or do you still need more help here? Do let us know. In case the given solution was helpful, then kindly choose it as the 'Correct Reply'.

    Thanks!

    Sukrity Wadhwa
    Terms & ConditionsAccessibility statement

    Loading footer...