Insert without explicit columns

1

I have several tables T1, T2 ... TN, each one can have columns in common or not. My final table F contains all the columns (one time only) of the previous ones.

How can I insert of each one without having to explain which columns I'm inserting?

For example, let's say that table T1 has columns A and B. Table F has A, B, C, and D.

I would like something of the type

insert into F select * FROM T1;

or

insert into F select A,B,C,D FROM T1;
    
asked by anonymous 08.12.2015 / 15:01

1 answer

0

No. You can not do INSERT without setting the columns. When you use

INSERT INTO tabela VALUES(a, b, c) 

You are implicitly saying that you will use ALL columns in the order in which they were created.

But you can use this to do things like:

INSERT INTO a SELECT a, null, 'X' FROM  b WHERE condicao 

As long as your columns accept NULL and a value passed as the default for each line can accept 'X' as the value.

A more practical example to understand: Assuming B = (id_A, tp_Sangue, factor_RH) and A = (id_A, NAME, SEX)

INSERT INTO B SELECT id_A, 'AB', 'RH+' FROM A WHERE SEXO='F';

We will be assigning 'AB' and 'RH +' to all records with SEX = 'F';

Another way to do something like explained about INSERT INTO F SELECT * FROM T1; would be to create a VIEW containing the fields equal to F from T1;

    
08.12.2015 / 15:37