.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Melody Anderson

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to duplicate records in sql according to a specific a criteria ?

Posted By:Kemal AL GAZZAH       Posted Date: June 23, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This article shows how to duplicate data n times, n depends on specific creteria for each record

How to duplicate records in sql according to a specific a criteria ?

A colleague to me asked me to help in creating a sql script which displays
duplicated records as per a specific criteria

Type1  has a periodocity of "monthly" and should displayed 12 times
Type 2 has a periodicity of  "each 3 months" and should be displayed 4 times
Type 3 has a periodicity of "each 6 months" and should be displayed 2 times

we have a Table called Tab1 having records with data of type among these 3 ones

The solution I gave him is the following:

1) Create a table with numbers varying from 1 to 12 called tab_frequencies
create table tab_frequency( freq as in)
insert into tab_frequency(freq) values (1)
insert into tab_frequency(freq) values (2)
insert into tab_frequency(freq) values (12)

2) Create a table linking the types with frequencies called tab_freq_types
create table tab_freq_types (type nvarchar(10),freq int)

insert into tab_freq_types (type,freq) values('Type 1','12')
insert into tab_freq_types (type,freq) values('Type 2','4')
insert into tab_freq_types (type,freq) values('Type 3','2')

Then to dispaly the data in Tab1 duplicated as per the corresponding frequencies, I  mad the following script

select t.*,tf.freq from tab1 t
inner join tab_freq_types tft on t.type=tft.type
inner join tab_frequency tf on tft.freq <= tf.freq

The important point here was to make a jointure based on INEGUALITY and not to an eguality (which most commonly used)


 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend