0

I have a table and i would like to replicate/clone records within the same table. However i would like to do that with a condition. And the condition is i have a column called recordcount with numeric values. For example Row 1 can take on a value of recordcount say 7, then i would like my row 1 to be replicated 7 times. Row 2 could take on a value say 9 then i would like row 2 to be replicated 9 times. Any help is appreciated. Thank you

Thom A
  • 88,727
  • 11
  • 45
  • 75
VenkatR
  • 3
  • 1
  • 1
    Welcome to Stack Overflow. When asking a question, it's important to get your tags right. You've tagged MySQL and SQL Server here, 2 completely different RDBMS. I've removed these tags, as it's unclear which you are using (and thus won't cause other volunteers confusion). However, it's important you [edit](https://stackoverflow.com/posts/52043579/edit) your question and add the correct tag and (most importantly) **only** the correct tag. Thanks. – Thom A Aug 27 '18 at 16:38
  • Give us your table structure – Madhur Bhaiya Aug 27 '18 at 16:38
  • Hi and welcome to SO. I would suggest that you don't really want a bunch of duplicate rows in your table. That defeats the concept of relational data. You also need to decide if you are using mysql or sql server. – Sean Lange Aug 27 '18 at 16:38
  • But if you really do think you want a bunch of duplicates the way to do this is with a tally or numbers table. The exact implementation would depend on your DBMS. Here is a great article that discusses the concept from a sql server perspective. http://www.sqlservercentral.com/articles/T-SQL/62867/ – Sean Lange Aug 27 '18 at 16:40
  • Thank you Larnu. I am using sql server. I am doing this to perform some statistical analysis for which i am required to replicate. Madhur as far as table structure goes i have 30 columns in my table right now. I dont really have to replicate within the same table if that makes this easy. I could create a new table. – VenkatR Aug 27 '18 at 16:50
  • I've added the tag back for you, please remember, as I said in the comment, to edit your post when asked; especially with important information like tags. Thank you. – Thom A Aug 27 '18 at 17:01

1 Answers1

0

What you can do (and I'm pretty sure it's not a best practice),

Is to hold a table with just numbers, which has rowcount that correspond to the numeric value.

Join that with your table, and project your table only.

Example:

create table nums(x int);
insert into nums select 1;
insert into nums select 2;
insert into nums select 2;
insert into nums select 3;
insert into nums select 3;
insert into nums select 3;

create table t (txt varchar(10) , recordcount int);
insert into t select 'A',1;
insert into t select 'B',2;
insert into t select 'C',3;


select t.*
from t 
    inner join nums
        on t.recordcount = nums.x
order by 1
;

Will project:

"A",1
"B",2
"B",2
"C",3
"C",3
"C",3
Y.S
  • 1,860
  • 3
  • 17
  • 30
  • Edit this in your post and format it so it would be clear, I (and probably anyone else who want to help) cannot understand it – Y.S Aug 27 '18 at 17:45
  • lets keep example simple. Lets say i have 3 columns. ID, recordcount and Age in my table. I want to create a new table with replicated rows. for each ID by recordcount. So if ID = 1 has recordcount = 7. Then i want that record replicated 7 times in my new table. – VenkatR Aug 27 '18 at 18:10
  • @VenkatR sounds like that's exactly what the code I answered does... all you need is just to add an "insert into " before the select – Y.S Aug 27 '18 at 18:14
  • have a look here https://stackoverflow.com/questions/8560619/sql-server-select-into-versus-insert-into-select – Y.S Aug 27 '18 at 18:19