Duplicating data in the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Duplicating data in the same table

Author

Topic

mkp2004
Starting Member

8 Posts

Posted - 2004-05-27 : 15:47:08

Iam migrating some data from one sql server to another.My problem is they don't have lots of data so they want to duplicate data but only one value will be different in the same table..An eg:

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-27 : 19:49:26

If the ID's don't matter, then you can create a DTS transform data task that uses the following ActiveX transformation script:

dim nCounter
nCounter = 0

Function Main()
if nCounter < 10 then
Main = DTSTransformStat_SkipFetch
DTSDestination("ID") = DTSSource("ID")
if nCounter = 0 then
DTSDestination("EmployeeID") = DTSSource("EmployeeID")
else
DTSDestination("EmployeeID") = DTSSource("EmployeeID") & "-" & nCounter * 1000
end if
DTSDestination("Name") = DTSSource("Name")
DTSDestination("Age") = DTSSource("Age")
DTSDestination("Sex") = DTSSource("Sex")
nCounter = nCounter + 1
else
nCounter = 0
Main = DTSTransformStat_SkipInsert
end if

End Function

HTH,

Tim

mkp2004
Starting Member

8 Posts

Posted - 2004-05-28 : 14:41:11

Thanks a lot for the help...Thats great...One more thing if employee id in the destination is varchar(though its a number its type is varchar) and Now i want to convert the corressponding source field employee in to a long and then add the 1000,2000....etc..and then I need to convert it back to varchar before I send it across to the destination...so its basically a converion and then doing the calculation and then coversion again to the varchar...Please help me with that also...Once again thanks a lot...
regards,
mkp2004

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-30 : 19:13:04

More of a programming issue than a database one, but here goes:

DTSDestination("EmployeeID") = CLng(DTSSource("EmployeeID")) * nCounter * 1000

You don't need to explicitly convert the number back to a varchar, as the DB will do this for you.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources