Duplicating records

[mkp2004]

I want to duplicate each record 100 times in a table with a different unique key(not a auto generated one).Basically say a table in sql server has 100 records with a unique loan number(not auto generated)then I wanted to duplicate each record 100 times with only the change in the loan number.Please do help me....
regards,
mkp2004

 

[donutman]

I think this should work:

Insert into MyTable
   Select NewID(), AllOtherFields from MyTable 
      cross join (Select Top 100 AnyOneField from AnyExistingTable)

The point is to "multiply" MyTable by 100, so use a table that you know has more than 100 rows.
-Karl

 

[maswien]

Assume:

1. The table you want duplicate:
mytable (loan_number, A, B, C )
2. The table has 100 records and unique loan number:
loans (loan_number)

So the SQL you want is:

insert into mytable
select * from
(select * from loans) t1,
(select A, B, C from mytable)

 

[mkp2004]

I think the way the sql team understood was different..Hope this will explain it more...

employee table

id Employee_id Name age Sex
1 300 Manu 23 m
2 301 Anu 45 f
3 302 Vinu 56 m


Now I want to duplicate the records in this table just by changing the employee_id(eg:- adding 1000 to the 1st 3records.Now I will have a data set.Like that I want to make 100 sets.It will look like....

employee table

id Employee_id Name age Sex
1 300 Manu 23 m
2 301 Anu 45 f
3 302 Vinu 56 m
4 300+1000 Manu 23 m
5 301+1000 Anu 45 f
6 302+1000 Vinu 56 m
7 300+2000 Manu 23 m
8 301+2000 Anu 45 f
9 302+2000 Vinu 56 m
.........................................
.........................................

Like that 10 sets of data.here if u notice the only change is the employee_id where it should be made in a loop to generate 10 sets.

This is needed as a creteria for DTS..So please help me very urgently

 

[maswien]

You can do it using following script:

declare @i as int
while @i < 101
begin
insert into employee
select employee_id + 1000 from employee

set @i = @i + 1
end

 

[maswien]

To be more accurate, it should be:


declare @i as int
set @i = 1
while @i < 101
begin
insert into employee
select employee_id + 1000, ... from employee

set @i = @i + 1
end

 

[maswien]

please ignore my above reponse, they are not right.

 

[donutman]

Is there a reason you can't use NewID(). Isn't this just for testing anyway? Any method like +1000 and +2000 depends on the existing data i.e. the current range of employee_id.
If you insist then

Create Table #Temp (ID int)
Declare @Cnt as int
Set @Cnt=1000
While @Cnt<100000
   Begin
      Insert into #Temp
         Select @Cnt
      Set @Cnt=@Cnt+1000
   End
Insert into Employee
   Select E.ID, T.ID+E.Employee_id, [Name], Age, Sex
   From Employee E cross join #Temp
Drop table #Temp

-Karl

 

[maswien]

create another table: loans(ad int)
populate it with : 1000 ~ 100,000
They are all the number you will use to add to employee_id


It should be:

insert into employee (employee_id, ename)
select ad + employee_id, ename from
( select * from loans) t1,
( select employee_id, ename from employee ) t2

 

[mkp2004]

Hi there..thanks a lot..This Query is working in the query analyzer perfectly but as I use the same query in the Execute SQl task in the Dts package it says invalid object name #Temp and its not even parsing it properly.PLease help me with that

 

[donutman]

Were you planning to use this on any other server? The reason I ask is because you could just create a DummyTable instead of a temporary table. Populate it once and reuse it as needed. Then the Dts package would only need the Insert statement.
I don't know why you're getting the error, but I'm guessing it doesn't like temporary tables.
-Karl