0

Create SQL Query or procedure for coping all rows from table A to table B. You need to generate a new ID for each row in table B. Then you need to store this new ID in table A as a foreign key.

zed
  • 3
  • 4
  • https://stackoverflow.com/questions/13237623/copy-data-into-another-table did you checked the question – sawan Aug 05 '22 at 10:53
  • As you are generating the new id and want to link that id as a foreign key in table A, IDs in table A and B will be different, then why do you want foreign key – sawan Aug 05 '22 at 10:58
  • ID_A != ID_B, but foreign key ID_A_FOREIGN = ID_B. I want to connect this two tables. And ID_B must be generated something like this ```SELECT NEXT VALUE FOR NZ.SEQ_CLOB_TABLE FROM SYSIBM.SYSDUMMY1;``` – zed Aug 05 '22 at 11:01
  • What does it mean "to store this new ID in table A"? Does it mean, that `A` has empty column `ID_A_FOREIGN`, which needs to be filled up upon `INSERT INTO B` with generated `ID_B`? Somehow else? – Mark Barinstein Aug 05 '22 at 12:24
  • yes. I want to copy all rows to table B. Generate ID_B. And then update that table A Foreign key with ID_B. – zed Aug 05 '22 at 13:34
  • ```CREATE PROCEDURE insertToClobFromObdelavaNapak() BEGIN DECLARE num INT; SELECT count(*) FROM nz.OBDELAVA_NAPAKA on2 INTO num; END``` I can't even store into varible like this in DB2. – zed Aug 05 '22 at 13:40
  • It's much easier to do this as `UPDATE A SET ID_A_FOREIGN = ROW_NUMBER () OVER ()` and `INSERT INTO B (..., ID_B, ...) SELECT ..., ID_A_FOREIGN, ... FROM A`. You don't need any procedural logic for this. Even both these statements can be combined into a single one. – Mark Barinstein Aug 05 '22 at 17:49

0 Answers0