Script to duplicate data for a Single Value

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

Transact-SQL (2000)

Script to duplicate data for a Single Value

Author

Topic

mkp2004
Starting Member

8 Posts

Posted - 2004-06-02 : 20:26:24

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
Starting Member

8 Posts

Posted - 2004-06-02 : 20:39:00

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-02 : 20:55:54

The following script would basically do what you want:

declare @lastID varchar(13),
@name varchar(20),
@seq int,
@numdup int

set @seq = 1
set @numdup = 4

DECLARE C1 CURSOR
READ_ONLY
FOR
SELECT emp_id, emp_name
FROM Testtable
WHERE emp_id not like '005...%'

OPEN C1

FETCH NEXT FROM C1 INTO @lastid, @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
WHILE (@numdup > 0)
BEGIN
Insert Into TestTable (emp_id, emp_name) values ('005.....' + convert(varchar, @seq), @name)
SET @seq = @seq + 1
SET @numdup = @numdup - 1
END
FETCH NEXT FROM C1 INTO @lastid, @name
SET @numdup = 4
END

CLOSE C1
DEALLOCATE C1

HTH,

Tim

mkp2004
Starting Member

8 Posts

Posted - 2004-06-02 : 22:29:56

Is there any other way than using cursor.Can we make a script which can do the same like using some temp table for creating the sequence and one more thing the sequence starts from 0050000000.please do help me with this

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-02 : 22:59:26

This should do it:

drop table #tmpTbl
create table #tmpTbl (emp_id int IDENTITY(0050000001,1), emp_name varchar(20), seq int)

insert into #tmptbl(emp_name, seq)
(
select emp_name, 1 from testtable UNION
select emp_name, 2 from testtable UNION
select emp_name, 3 from testtable UNION
select emp_name, 4 from testtable
)

insert into testtable (emp_id, emp_name)
select emp_id, emp_name
from #tmpTbl

drop table #tmpTbl
select * from testtable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources