SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id

Posted on

Question :

Suppose I have two tables name company and screensavers as given below:

**Company:**
id (pk auto inc)
name (varchar)

**ScereenSavers:**
id (pk auto inc)
name (varchar)
path (varchar)
company_id (int) # This is pk of Company

Now I want to Select each company and add 3 records which means every single company’s pk will be saved as fk in screensaver table for the given number of users. How can I doe this by writing something like:

SELECT id AS c_id From company
INSERT INTO screen_savers VALUES (
    name='Screen Saver-I',
    path='screen_save_i_path',
    company_id=c_id
),(
    name='Screen Saver-II',
    path='screen_save_ii_path',
    company_id=c_id
),(
    name='Screen Saver-III',
    path='screen_save_iii_path',
    company_id=c_id
)

I don’t know above will work, but I want to give some idea what I wanted to do. Can someone please let me know the solution?
I am using Postgresql.

Answer :

The Postgrel documentation is fairly good. The most relevant is Data-Modifying Statements in WITH. Taking the documentation and your example above the following SQL should do what you want. Tested on Postgrel 9.6.

CREATE TABLE Company (
   CompanyID serial PRIMARY KEY,
   CompanyName VARCHAR (50)
) ;

CREATE TABLE ScreenSavers (
  ScreenSaverID serial PRIMARY KEY,
  ScreenSaverName VARCHAR (50),
  path VARCHAR (50),
  CompanyID serial
) ;

INSERT INTO Company (Name) Values ('CompanyA');
INSERT INTO Company (Name) Values ('CompanyB');

WITH MultiInsert AS(
     SELECT 
        'Screen Saver-I' AS ScreenSaverName,
        'screen_save_i_path' AS ScreenSaverPath
   UNION
     SELECT 
        'Screen Saver-II' AS ScreenSaverName,
        'screen_save_ii_path' AS ScreenSaverPath
   UNION
    SELECT 
        'Screen Saver-III' AS ScreenSaverName,
        'screen_save_iii_path' AS ScreenSaverPath
  )
 INSERT INTO ScreenSavers (Name,Path,CompanyID)
  SELECT  
    MI.ScreenSaverName,
    MI.ScreenSaverPath,
    CO.CompanyID
  FROM 
    MultiInsert MI ,Company CO
  ORDER BY 
    MI.ScreenSaverName;

  SELECT  
    ScreenSaverID,
    Name,
    Path,
    CompanyID
  FROM
    ScreenSavers
   ORDER BY ScreenSaverID;

I renamed the some of the columns to be more descriptive.

While this works I feel it lacks elegance.

Leave a Reply

Your email address will not be published. Required fields are marked *