Insert a tableA into another tableB with a different structure

Question

I have a table A looks like :

 id isin    typ1    typ2                                                                                 
  1 aa      typA    typB                                                                                 
 2  bb      typD    typC

I need to insert into a table B that looks like:

id  isin    fld     value                                                                                
 1   aa     typ1    typA                                                                                 
 2   aa     typ2    typB                                                                                 
 3   bb     typ1    typD                                                                                 
 4   bb     typ2    typC

Can I do with an SQL instruction?

@Giovanni . . . Tag your question with the database you are using. — Gordon Linoff, Nov 18 '20 at 12:49

score 1 · Answer 1 · answered Nov 18 '20 at 09:21

The most generic approach is union all:

insert into tableb (id, isin, fld, value)
select id, isin, 'typ1', typ1 from tablea
union all select id, isin, 'typ2', typ2 from tablea

If your database, which you did not disclose, supports lateral joins, this can be more efficiently done using this feature. A typical syntax would be:

insert into tableb (id, isin, fld, value)
select a.id, a.isin, x.fld, x.value
from tabla a
cross join lateral (values ('typ1', typ1), ('typ2', typ2)) as x(fld, value)

The exact syntax for lateral joins varies across databases.

i'm using MS Acces and the first solution does not work, I do not know why.... The problem seems linked to the INSERT INTO part... — Giovanni, Nov 18 '20 at 10:57

score 0 · Answer 2 · answered Nov 18 '20 at 20:53

You can use ROW_NUMBER() Analytic function along with UNION ALL in order to insert the values unpivoted and id values renumerated

INSERT INTO tableB(id, isin, fld, value)
SELECT ROW_NUMBER() OVER (ORDER BY isin, fld), A.* 
  FROM 
  (
   SELECT isin, 'typ1' AS fld, typ1 AS typ FROM tableA UNION ALL
   SELECT isin, 'typ2'       , typ2        FROM tableA
  ) AS A

Insert a tableA into another tableB with a different structure

2 Answers2