SQL For Loop query

Question

I am newbie to SQL.

I want to insert data into table using below for-loop query. But unable to identify the issue

declare
cursor mac is SELECT DISTINCT(MAC) FROM DEVICES;
cmd varchar2(200);
begin
for c in mac loop
cmd := 'INSERT INTO MAC VALUES(DEVICES_ID_SEQ.nextVal,'||c.MAC||',"ABC","123")';
execute immediate cmd;
end loop;
end;

For each MAC in existing table, I want to insert new record.

Don't use a cursor or PL/SQL if it's not needed, please! Look at Quassnoi's answer for something that is MUCH faster and MUCH less code. Learn to do things the set-based way using SQL. — Colin 't Hart
– Colin 't Hart, Commented Apr 29, 2013 at 9:42

Quassnoi · Accepted Answer · 2013-04-29 09:32:45Z

7

You don't need a cursor for that.

Just run:

INSERT
INTO    mac
SELECT  DEVICES_ID_SEQ.nextval, mac, 'abc', 123
FROM    (
        SELECT  DISTINCT
                mac
        FROM    devices
        )

answered Apr 29, 2013 at 9:32

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Colin 't Hart Over a year ago

Another great example of less is more. The OP 1) uses dynamic SQL (not needed); 2) probably has a bug with the MAC address being a string and therefore should be quoted in the dynamic SQL; 3) is somewhat susceptible to overflowing the string buffer of just 200 characters; 4) uses PL/SQL and a cursor where it's not needed anyway.

Collectives™ on Stack Overflow

SQL For Loop query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related