By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,227 Members | 1,025 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,227 IT Pros & Developers. It's quick & easy.

insert same row multiple times

P: n/a
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

Feb 20 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Feb 20 '07 #2

P: n/a
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?

Feb 20 '07 #3

P: n/a
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)

Feb 21 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.