Need to ADD a duplicate row.

[Chuck712]

I am working on a project that involves a membership drive and a contest to place people into a "drawing". This is based on renewing their membership and if they renew for 2 years, then they get 2 "entries". I was able to give them a value of 2 on the row, but now I need to create a temp table that will add them twice. Any advice?

 

[bborissov]

Could you post some simple value and desired result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Chuck712]

Thanks. Sure, here would the the results from the query. Very simple
ID Name Years Renewed
100842 CsrmMn P Libby 2
101149 MmilM L Libby 2
103420 Gvds J ViAMllsro 1
104139 MilMMn M Sood 1
104151 BMAAy L SmsrA 1
104315 Arvdi KrsSczyk 1

Here is all the I need back
100842 CsrmMn P Libby
100842 CsrmMn P Libby
101149 MmilM L Libby
101149 MmilM L Libby
103420 Gvds J ViAMllsro
104139 MilMMn M Sood
104151 BMAAy L SmsrA
104315 Arvdi KrsSczyk

The end user will load the results into an excel spread sheet and create a random column for the drawing.

 

[bborissov]

And no bigger value than two?
If so:

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=blue]Name[/color] [COLOR=blue]varchar[/color](200), [COLOR=blue]Number[/color] [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](100842,[COLOR=red]'CsrmMn P Libby'[/color],2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](101149,[COLOR=red]'MmilM L Libby'[/color],2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](103420,[COLOR=red]'Gvds J ViAMllsro'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](104139,[COLOR=red]'MilMMn M Sood'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](104151,[COLOR=red]'BMAAy L SmsrA'[/color],1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](104315,[COLOR=red]'Arvdi KrsSczyk'[/color],1)


[COLOR=blue]SELECT[/color] *
[COLOR=blue]FROM[/color] @Test
UNION ALL
[COLOR=blue]SELECT[/color] *
[COLOR=blue]FROM[/color] @Test
[COLOR=blue]WHERE[/color] [COLOR=blue]Number[/color] > 1
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Id

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Chuck712]

Thanks. That gives me a start. The query is returning between 4000 and 5000 rows, so using an insert might be a little time consuming and prone to errors. I appreciate the help.

 

[RiverGuy]

For electronic drawings, I prefer to randomly select from a set of data with one row per contestant. Something like

Contestant  Entries  RangeStart  RangeEnd
----------  -------  ----------  --------
Bill        2        1           2
Ted         1        3           3
Mary        2        4           5
Frank       5        6           10

Then you can select the row where your random number falls within the range. Simple Excel formulas could easily create the RangeStart and RangeEnd columns or you could do it in SQL as well.