CODEWITHSUNDEEP
sqloracle-databaseloopssql-insert
vinr
vinr

Reputation: 25

SQL loop inserts

I have a company table with list of companies name and company id.

Now there is a Value table which hold information about the company with reference to company id.

I need to first get the list and size of the companies and for all the companies insert a particular feature information in the Value table.

This means I need to have all companies having those features in the Value table.

I tried to use the below SQL which gives a compilation error. But the for loop works well without the insert.

DECLARE
  x NUMBER(2) ;
BEGIN 
    FOR x IN (select distinct company_num from company where comp_IN_comp='T') LOOP
       INSERT INTO VALUE (PROPERTY_NUM, DATA_GROUP, NUM_UPDATES, 
        CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, VALUE) VALUES 
        ('78', x ,'0', TO_DATE('2015-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
        'ADMIN', TO_DATE('2015-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'ADMIN', 'N');
    END LOOP;
END;

Upvotes: 1

Views: 129

Answers (1)

Mureinik
Mureinik

Reputation: 312329

You don't need a loop for this - just use an insert-select statement:

INSERT INTO VALUE (PROPERTY_NUM, 
                   DATA_GROUP, 
                   NUM_UPDATES, 
                   CREATED_DATE, 
                   CREATED_BY, 
                   LAST_UPDATED_DATE,
                   LAST_UPDATED_BY,
                   VALUE)
SELECT             DISTINCT '78',
                   company_num,
                   '0',
                   TO_DATE('2015-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                   'ADMIN',
                   TO_DATE('2015-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                   'ADMIN',
                   'N'
FROM               company
WHERE              comp_in_comp='T'

Upvotes: 2

Related Questions