Peter Penzov Peter Penzov - 6 months ago 10
SQL Question

Insert data with one SQL query

I use these tables to store user credentials and roles:

CREATE TABLE ACCOUNT(
ID INTEGER NOT NULL,
USER_NAME TEXT NOT NULL
)
;

ALTER TABLE ACCOUNT ADD CONSTRAINT KEY1 PRIMARY KEY (ID)
;

CREATE TABLE ACCOUNT_ROLE(
ID INTEGER NOT NULL,
USER_NAME TEXT NOT NULL
)
;

CREATE INDEX IX_RELATIONSHIP19 ON ACCOUNT_ROLE (ID)
;

ALTER TABLE ACCOUNT_ROLE ADD CONSTRAINT KEY26 PRIMARY KEY (ID)
;


As you can see I use the same ID for the both tables. How I can insert data into these tables with only one SQL query?

Answer

You can use with statement, e.g.:

with insert_into_account as (
    insert into account values (1, 'John Doe')
    )
insert into account_role values (1, 'John Doe');

Note, that the index ix_relationship19 is redundant, as primary key creates unique index.