New to SQL , I have a table called DataSetFact
with a column called data
data |
---|
SOCIETE;SSY;Societe SSY |
SOCIETE;XXX ;Societe XXX |
ETABLISSEMENT;SITE_AV;Aveyron;AAT;SITE_AV |
DEPOT;FRCAN ;ENTREPOT FOURNISSEUR FRCAN |
GRP;APPMESURE;SUIVI APPAREILS DE MESURE |
ARTICLE;59000013;REFERENCE Jumbo;MATPREMFAB;Article |
ARTICLE;59000015;REFERENCE Jumbo222;MATPREMFAB222;Article |
I want from the column data
create 5 tables & Columns base on ;
seperator
Table names are the first value SOCIETE
, ETABLISSEMENT
, DEPOT
, GRP
, ARTICLE
For example for table SOCIETE
two columns would be created with values (SSY , Societe SSY)
CodePudding user response:
Create the tables as needed:
create table societe (ssy ..., societe_ssy ...);
create table etablissement (...);
Then you can use split_part
extract the columns from each string in the source table:
insert into societe (ssy, societe_ssy)
select split_part(data, ';', 2),
split_part(data, ';', 3)
from datasetfact
where data like 'SOCIETE%';
create table etablissement (...)
insert into etablissement (...)
select split_part(data, ';', 2),
split_part(data, ';', 3),
split_part(data, ';', 4)
from datasetfact
where data like 'ETABLISSEMENT%';