query to copy dataset with modification

[DavidSlaw1]

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>

[supratim320 · Accepted solution]

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]

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!