-1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.