Insert Data in 3 Tables At a Time Using Postgres

Data insertion into multiple tables with one query using postgres

postgreSql has different syntax for that. I was stuck in this also a few days back figured out how to deal with it. You can user BEGIN block to insert data into multiple tables for that you please follow this query and your work will be done.

BEGIN;
                WITH userins AS (
                INSERT INTO public.user(
                ""FirstName"", ""LastName"", ""Email"", ""PasswordHash"",""VerificationCode"", ""Phone"", ""IsEnabled"", ""IsVerified"", ""IsDeleted"",  ""CreatedDate"")VALUES(@FirstName, @LastName, @Email, @PasswordHash, @VerificationCode, @Phone, @IsEnabled, @IsVerified, @IsDeleted, @CreatedDate)
                    RETURNING id AS user_id)
                ,clientins AS(
                INSERT INTO public.client(""Description"", ""PlanCustomerId"", ""IsActive"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", UserId) 
                        VALUES(@Description, @PlanCustomerId, @IsActive, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT user_id from userins)) RETURNING id as client_id)
                ,clientsubins AS
                (
                   INSERT INTO public.client_subscription(""PlanId"", ""IsActive"",""StartDate"", ""EndDate"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", ClientId, SubscriptionId)
                   VALUES(@PaymentMethodPlanId, @IsActive, @StartDate, @EndDate, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT client_id from clientins), @PlanId)
                )
                ,clientpurchaseins AS
                (
                   INSERT INTO public.client_purchase_history(""PlanId"", ""InvoiceId"",""StartDate"", ""EndDate"", ""IsDeleted"", ""CreatedBy"", ""CreatedDate"", ClientId)
                   VALUES(@PlanId, @InvoiceId, @StartDate, @EndDate, @IsDeleted, (SELECT user_id from userins), @CreatedDate, (SELECT client_id FROM clientins))
                )
                SELECT client_id from clientins;
        COMMIT;

Here is Begin block will take care of your transaction if there is any error occurred then it will be rollback automatically.

Inserting into lots of tables at once

Using a function will work well, just as running several INSERT statements in a single transaction or a stack of CTEs using INSERT INTO ... SELECT ... RETURNING ....

Insert into 2 tables with single SQL statement instead of loop

Filling in missing bits with assumptions, it could work like this:

WITH description_insert AS (
   INSERT INTO description
         (label         , lang_id, poi_id)
   SELECT c.rdfslabelfrs, 1      , p.id
   FROM   csv_poi_rdf_fr c
   JOIN   poi p ON p.uri_id = c.poi
   RETURNING poi_id, id
   )
INSERT INTO poi_titre_poi (poi_id, titre_poi_id, titre_poi_key)
SELECT d.poi_id,  d.id , 'fr'
FROM   description_insert d;

Related:

• PostgreSQL multi INSERT...RETURNING with multiple columns
• Insert data in 3 tables at a time using Postgres
• Get Id from a conditional INSERT

PostgreSQL - Insert data into multiple tables simultaneously

The idea is to write WITH clauses that contain INSERT ... RETRUNING to return the generated keys. Then these “views for a single query” can be used to insert those keys into the referencing tables.

WITH par_key AS
        (INSERT INTO participante (nome) VALUES ('Laurenz') RETURNING id),
     ven_key AS
        (INSERT INTO venda (inicio) VALUES (current_date) RETURNING id),
     item_key AS
        (INSERT INTO item (nome) VALUES ('thing') RETURNING id)
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valor)
   SELECT ven_key.id, item_key.id, par_key.id, numeric '3.1415'
   FROM par_key, ven_key, item_key;

PostgreSQL INSERT INTO table multiple records taken from multiple selects

Try replacing , with a union/union all after each select

BEGIN;
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('{}') RETURNING recipe_id
), ingredient1 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
), ingredient2 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
), ingredient3 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
)
INSERT INTO recipes_ingredients (recipe_id, ingredient_id) 
SELECT new_recipe.recipe_id, ingredient1.ingredient_id FROM new_recipe CROSS JOIN ingredient1 Union
SELECT new_recipe.recipe_id, ingredient2.ingredient_id  FROM new_recipe CROSS JOIN ingredient2 Union
SELECT new_recipe.recipe_id, ingredient3.ingredient_id  FROM new_recipe CROSS JOIN ingredient3;
COMMIT;

   

SQL INSERT INTO from multiple tables

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON p.Id = n.Id

---

Related Topics