On 2010-11-16, Andreas <maps...@gmx.net> wrote: > Hi, > > I frequently get tables from spreadsheets to import into the DB. > > Usually it looks like this: > A1, A2, A3, A4, B1, B2, B3, with optional C1, C2, D1, D2, ... > > and there is a 1:n relation between A and B. > If provieded the C would be 1:1 to A and D 1:1 to B. > > Up until now I let a VBA script order the source table by A, then scan > the table line by line and create a new entry in the target table A* and > fetch its serial ID everytime the script figures that A changed. > With this IDa create 1 C* and as many B*s until A changes again ... and > of course fetch IDb to attach the D* records with a foreign key column. > > Now I'm trying to get away w/o the VBA stuff. > > Is there a clever way to split such denormalized sources while still > obtaining the needed IDs to connect everything?
create a temporary table and put the IDS in there. then do a select distinct to populate table B, do an update from to put the IDs from B into the temp table then do a select to populate table A etc... -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
