Pass Variable into Query

[phatz]

ok not sure if this gives you an idea.

I have the following (for example use, not realworld but using this for the example) select statement which I can use with an Insert

Insert into tableK(name)
select name from tableZ where name = 'Gofer'

I want to dynamically do this using a variable I have populated with a single row of data from a select statment before running the main select statement as it were

So for example

Declare newname varchar2(20)
newname = select surname from tableB where name = 'PHATZ'

Insert into tableK (name)
select name from tableZ where name = newname

Can someone give me a basic interpretation of this and how it would look in a script?

Many Thanks

[OraMaster]

ok not sure if this gives you an idea.

I have the following (for example use, not realworld but using this for the example) select statement which I can use with an Insert

Insert into tableK(name)
select name from tableZ where name = 'Gofer'

I want to dynamically do this using a variable I have populated with a single row of data from a select statment before running the main select statement as it were

So for example

Declare newname varchar2(20)
newname = select surname from tableB where name = 'PHATZ'

Insert into tableK (name)
select name from tableZ where name = newname

Can someone give me a basic interpretation of this and how it would look in a script?

Many Thanks

This is not a dynamic type of SQL you are talking about.
Below code will work for you.

declare
newname varchar2(20);
begin
select surname into newname from tableB where name = 'PHATZ';

insert into tableK(name) 
select name from tableZ where name = newname;

commit;
end;

[phatz]

Many thanks for this, how can I add a commit every 50000 rows to this, just to add the icing on the cake as it were.

Many Thanks

[OraMaster]

Try using below code.

DECLARE
newname VARCHAR2(20); 
BEGIN
SELECT surname
  INTO newname
  FROM tableb
 WHERE NAME = 'PHATZ';
LOOP
INSERT INTO tablek
            (NAME)
   SELECT z.NAME
     FROM tablez z
    WHERE z.NAME = newname
      AND ROWNUM <= 50000
      AND NOT EXISTS (SELECT k.NAME
                        FROM tablek k
                       WHERE k.NAME = z.NAME);
IF SQL%ROWCOUNT = 0 THEN EXIT;
COMMIT; 
END LOOP;
END;