Situation:
There is the following table called P_FAMILIA
:
|ID | NAME | SOLDADOS |
|---|------------|------------|
| 1 | Stark | 30000 |
| 2 | Targaryen | |
| 3 | Lannister | 15000 |
| 4 | Greyjoy | |
It is required to build a sql query, to pass all the fields of the previous table (the data supplied is an example, it can have many more records) to another table called P_DIAFAMILIA
adding the number 1, that is, after executing the query get
|ID |IDP_FAMILIA| NAME | SOLDADOS |
|---|-----------|------------|------------|
| 1 | 1 | Stark | 30000 |
| 1 | 2 | Targaryen | |
| 1 | 3 | Lannister | 15000 |
| 1 | 4 | Greyjoy | |
Scheme
CREATE TABLE P_FAMILIA
(
ID INT,
NAME VARCHAR2(30 BYTE),
SOLDADOS FLOAT(126)
);
ALTER TABLE P_FAMILIA
ADD PRIMARY KEY (ID);
CREATE TABLE P_DIAFAMILIA
(
ID INT NOT NULL,
IDP_FAMILIA INT NOT NULL,
NAME VARCHAR2 (30 BYTE),
SOLDADOS FLOAT (126)
);
ALTER TABLE P_DIAFAMILIA
ADD( FOREIGN KEY (ID)
REFERENCES P_FAMILIA (ID));
-- Insert Statements
Insert into P_FAMILIA
(ID, NAME, SOLDADOS)
Values
(1, 'Stark', 30000);
Insert into P_FAMILIA
(ID, NAME)
Values
(2, 'Targaryen');
Insert into P_FAMILIA
(ID, NAME, SOLDADOS)
Values
(3, 'Lannister', 15000);
Insert into P_FAMILIA
(ID, NAME)
Values
(4, 'Greyjoy');
COMMIT;
Basic syntax, to insert all the records from one table into another is:
For this particular case, the query would be: