script to generate duplicate data for a single value 1

[mkp2004]

I have a single table with 2 emp_id and its data inside.Now I want to duplicate the dataset.


emp_id (varchar(13)) emp_name
-------------------- --------
0011275286 manu
0011275290 Anush



starting serious 005....01(its length should be only 13) and then say we have to generate 5 duplicates for each loans..Now it should start with this series and then the data corressponding will be the duplicated the same...The result after the script runs should be as shown below...


emp_id (varchar(13)) emp_name
-------------------- --------
0011275286 manu
0011275290 Anush
005.....01 manu
005.....02 manu
005.....03 manu
005.....04 manu
005.....05 Anush
005.....06 Anush
005.....07 Anush
005.....08 Anush

Please help me out its urgent...

 

[mkp2004]

One more thing..No duplicates should be there for the emp_id

 

[donutman]

You do have more than 2 rows of data in the table, right? It can be done with SQL alone but, a quick way to get the job done is to create a new table with the same schema but add an identity column that starts with the sequence # that you want. Then insert the contents of your table to be duplicated 4 times.
Then insert the duplicated data back into the original table but use the identity column as the emp_ID. If the seq #'s of the duplicates have to be in consecutive, then do the 4 copies into separate tables but adjust the starting identity (with an increment of 4).
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mkp2004]

PLease do help me with this script and I can't create another table ..possibly something like temp table another thing is that its not just 4 emp_id's may be it can be more and the series starts from 0050000000.Please do help me with this

 

[donutman]

Declare @Repeat as int
Set @Repeat=1
Create Table #Tmp
      (ID int identity (500000000,1), emp_ID varchar(13), emp_name varchar(30), etc)
While @Repeat<5
   Begin
      Insert into #Tmp
         select * from YourTable
      set @Repeat=@Repeat+1
   End
Insert into YourTable
   Select '00'+cast(ID as varchar(13)), emp_name, etc from #Tmp
Drop Table #Tmp

I'm assuming that the only criteria for seq # is that it be unique. Further I assume that there is no chance that we will get duplicate emp_ID when we start with 50000000.
I haven't double checked the syntax because I'm not in the office right now.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]