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