vendredi 24 juin 2016

SQL Split line during import - run INSRERT INTO twice for specific lines

let's assume a super-simplified data example:

ID    AKey   AVal
-----------------
1     AB     94
2     Q      48
3     Z      56
4     AB     12
5     T      77
...   ...    ...

I would like to split "AB" into separate lines "A" and "B" in my import script, where I typically do:

INSERT INTO MyNewTable
SELECT 
   SRC.ID as OldIDRef,
   SRC.AKey as NewKey,
   SRC.AVal as NewVal
FROM OldTable as SRC

So basically, I would like to duplicate the "AB" lines in the select and perform some calculations specific for this line (let's say divide AVal by 2)

The only solution I can think of would be someting like this:

INSERT INTO MyNewTable
SELECT 
   SRC.ID as OldIDRef,
   CASE SRC.AKey = 'BA' THEN SUBSTRING(SRC.AKey,1,1) END as NewKey,
   CASE SRC.AKey = 'BA' THEN SRC.AVal / 2 END as NewVal
FROM OldTable as SRC

UNION ALL

SELECT 
   SRC.ID as OldIDRef,
   SUBSTRING(SRC.AKey,2,1) as NewKey,
   SRC.AVal / 4 + 10 as NewVal
FROM OldTable as SRC
WHERE SRC.AKey = 'BA'

I will need such process more then few times in my imports, so I wonder, If I am not missing some simpler solution?

Aucun commentaire:

Enregistrer un commentaire