Incremental data duplication - SQL Server Forums

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)

Incremental data duplication

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-04 : 02:15:38

I have a table with id and its duplication that should be used in table 2.Now I have table generated with the duplication_id for the corressponding id.Scripts for table 1 I have

Table1

id Duplication_id
---------------------------
1A 500
1A 501
1A 502
7D 503
7D 504
9G 505
9G 506

In table 2 I need to do a join with a table 1 so that I will generate a duplicate data for t he series as shown below.
Now the key is the primary key and its not a auto number so when i duplicate i should be able to do max(key)+1 so that as u duplicate just the data for each series,the key get incremented each time.The final out out looks like as given below

Table 2

key id item
-------------------------
1 1A cell
4 7D watch
6 9G camera

7 500 cell
8 501 cell
9 502 cell
10 503 watch
11 504 watch
12 505 camera
13 506 camera

please help me out

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-04 : 03:10:06

something like this maybe:

declare @maxkey int
set @maxkey = (select max(key) from table2)

create table #test(key int identity, duplication_id int, item varchar(50))

insert into #test
select a.duplication_id, b.item
from table1 a
join table2 b
on a.id = b.id

select @maxkey + key, duplication_id, item
from #test

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources