I have a 3 postgres tables as mentioned below,
CREATE TABLE document_type
(
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
status SMALLINT NOT NULL
);
CREATE TABLE opco
(
id SERIAL PRIMARY KEY,
opco_name VARCHAR(50) NOT NULL,
status SMALLINT NOT NULL
);
CREATE TABLE opco_document_type
(
opco_id INT NOT NULL,
document_type_id INT NOT NULL,
FOREIGN KEY (opco_id)
REFERENCES opco(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (document_type_id)
REFERENCES document_type(id)
ON DELETE CASCADE ON UPDATE CASCADE
);
I have already populate the opco and document_type tables with sample data. Now I need to populate the opco_document_type table in a way where each opco row should be mapped with entire document_type rows. I can populate the table manually, But it's taking time. Ex, if both opco and document_type tables have 100 rows each, then opco_document_type should have 100 x 100 rows.
I tried using below query, but I couldn't complete it.
INSERT INTO opco_document_type(opco_id, document_type_id)
SELECT op.id, doc.id
FROM opco op