insert same row multiple times

[Ultrak The DBA]

As a converted sybase dba, I have a question. In sybase a command can
be executed multiple times by placing a

go 1000

at the end of the statement. Such as:

insert into a (col1, col2)
values (1,2)
go 1000

this will put the values 1 and 2 in the respective columns for 1000
rows.

Is there an equivalent in db2 udb, v8.2.2?

Thank you,

Kermit Lowry

[Serge Rielau]

Ultrak The DBA wrote:

As a converted sybase dba, I have a question. In sybase a command can
be executed multiple times by placing a

go 1000

at the end of the statement. Such as:

insert into a (col1, col2)
values (1,2)
go 1000

this will put the values 1 and 2 in the respective columns for 1000
rows.

Is there an equivalent in db2 udb, v8.2.2?

Not through the driver, but you can write this many different ways:
--#SET TERMINATOR @
BEGIN ATOMIC
DECLARE i INT DEFAULT 0;
WHILE i < 100 DO
INSERT INTO T VALUES (1, 2);
END WHILE;
END
@
--#SET TERMINATOR ;

Or you can use recursion:
INSERT INTO T WITH rec(c1, c2, i) AS
(VALUES (1, 2, 0)
UNION ALL
SELECT c1, c2, i + 1 FROM rec WHERE i < 100)
SELECT c1, c2 FROM rec;

Or use the fact that DB2 supports multi row insert:
INSERT INTO T VALUES
(1, 2), .... (1, 2);
or use your shell

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[ChrisC]

Serge Rielau wrote:

Or you can use recursion:
INSERT INTO T WITH rec(c1, c2, i) AS
(VALUES (1, 2, 0)
UNION ALL
SELECT c1, c2, i + 1 FROM rec WHERE i < 100)
SELECT c1, c2 FROM rec;

Interesting. If you needed to use a CTE (with or without recursion)
for a DELETE or UPDATE statement, what would that look like? Or is it
even possible?

[Tonkuma]

INSERT INTO T
SELECT 1, 2
FROM (VALUES 0,1,2,3,4,5,6,7,8,9) p1(n)
,(VALUES 0,1,2,3,4,5,6,7,8,9) p2(n)
,(VALUES 0,1,2,3,4,5,6,7,8,9) p3(n)