Data Duplication for different IDs

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)

Data Duplication for different IDs

Author

Topic

mkp2004
Starting Member

8 Posts

Posted - 2004-06-01 : 23:12:19

I have 2 tables.

Table 1

emp_id Duplicated_emp_id
103 501
103 502
103 509
205 717
205 689
316 890
316 790
316 280
316 999

table 2 (Main Table)

emp_id emp_name emp_address
103 Manu adffsdfsd
205 anu dfsdfsdfs
316 vinu rttretre

First step is to check whether there is a corressponding emp_id in table 2
second is to duplicate as shown below.The data gets duplicated for each emp_id 103,205,316 but the data remains the same when

duplicated and only the emp_id changes.Can I do a Dts or is there a way to write a script that the final output looks like
shown below.

Now I need to duplicate the data set for each duplicate_emp_id.the final table should look like...

emp_id emp_name emp_address
103 Manu adffsdfsd
501 Manu adffsdfsd
502 Manu adffsdfsd
509 Manu adffsdfsd

205 anu dfsdfsdfs
717 anu dfsdfsdfs
689 anu dfsdfsdfs

316 vinu rttretre
890 vinu rttretre
790 vinu rttretre
280 vinu rttretre
999 vinu rttretre

Please help me out this...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-01 : 23:52:06

This might be the worst thing I've ever written. (grin) It's late and I'm foggy brained. It will get the job done though.

DECLARE @table1 TABLE(emp_id INT, duplicate_emp_id INT)

INSERT @table1(emp_id, duplicate_emp_id)
SELECT 103, 501
UNION ALL
SELECT 103, 502
UNION ALL
SELECT 103, 509
UNION ALL
SELECT 205, 717
UNION ALL
SELECT 205, 689
UNION ALL
SELECT 316, 890
UNION ALL
SELECT 316, 790
UNION ALL
SELECT 316, 280
UNION ALL
SELECT 316, 999

DECLARE @table2 TABLE(emp_id INT, emp_name VARCHAR(55), emp_address VARCHAR(55))

INSERT @table2(emp_id, emp_name, emp_address)
SELECT 103, 'Manu', 'adffsdfsd'
UNION ALL
SELECT 205, 'anu', 'dfsdfsdfs'
UNION ALL
SELECT 316, 'vinu', 'rttretre'

SELECT
a.emp_id,
c.emp_name,
c.emp_address
FROM (
SELECT emp_id FROM @table1
UNION
SELECT duplicate_emp_id AS emp_id FROM @table1) a
INNER JOIN @table1 b ON a.emp_id = b.emp_id
OR a.emp_id = b.duplicate_emp_id
INNER JOIN @table2 c ON b.emp_id = c.emp_id
GROUP BY
b.emp_id,
a.emp_id,
c.emp_name,
c.emp_address
ORDER BY
b.emp_id,
c.emp_name,
c.emp_address

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-02 : 09:03:09

select emp_id, emp_name, emp_address
from table2

union all

select table1.duplicated_emp_id, table2.emp_name, table2.emp_address
from table1
inner join table2
on table1.emp_id = table2.emp_id

basically the same as Derrick's method, maybe slightly shorter.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources