CODEWITHSUNDEEP
netezza
mamidipalli satish
mamidipalli satish

Reputation: 1

Netezza insert same row into table multiple times

i have table with the following values

location date count 2150 4/5/14 100

now i need to insert 100 rows into another table .The table should have 100 rows of

location date 2150 4/5/14

Help me in achieving this.My database is netezza

Upvotes: 0

Views: 1345

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

Netezza has a system view that has 1024 rows each with an idx value from 0 to 1023. You can exploit this to drive an arbitrary number of rows by joining to it. Note that this approach requires you to have determined some reasonable upper limit in order to know how many times to join to _v_vector_idx.

INSERT INTO target_table
SELECT *
FROM base_table
   JOIN _v_vector_idx b
   ON b.idx < 100;

Then if you want to drive it based on the third column from the base_table, you could do this:

INSERT INTO target_table
SELECT location,
   DATE
FROM base_table a
   JOIN _v_vector_idx b
   ON b.idx < a.count;

One could also take a procedural approach and create a stored procedure if you didn't have a feel for what that reasonable upper limit might be.

Upvotes: 1

Related Questions