s.sathish kumar s.sathish kumar - 6 months ago 15
SQL Question

Duplicating multiple records in table with primary key

I am working with duplicating multiple records in table 1 with primary key which has a reference with table 2 (foreign key)

Consider table

employee
with columns (eid(primary key),ename,dept,dept_code).

Table 2
employee_address
with columns (eaid(primary key), eid(foreign key), city, country)

Now the task is the table 1 (ie. employee) may have number of values with different department codes.

Suppose
dept_code
may be 1 or 2 like so and so.

Now with both the table with some data look like this

EMPLOYEE table

Eid(pk) ename dept Dept_code
100 Sss Cse 1
101 Aaa Cse 1
102 Bbb Cse 1
103 Ccc Eee 2
104 ddd it 3


EMPLOYEE ADDRESS table

Eaid (pk) Eid (fk) city country
1 100 NY Us
2 100 NY Us
3 100 NY Us
4 101 CALIF Us
5 102 DC Us
6 102 DC Us
7 103 NJ Us


now the thing is i have to duplicate all the records in employee table where dept_code=1 and duplicate records dept_code should be = 5.

And the foreign key referencing to employee address table (ie. here in the current document 100,101,102) in the table 2 has to be duplicated number of times those foreign key value present in table 2.

I have to get the output like this

Employee table

Eid(pk) ename dept Dept_code
100 Sss Cse 1
101 Aaa Cse 1
102 Bbb Cse 1
103 Ccc Eee 2
104 ddd it 3
**105 sss cse 5
106 aaa cse 5
107 bbb cse 5**


Employee Address table

Eaid (pk) Eid (fk) city country
1 100 NY Us
2 100 NY Us
3 100 NY Us
4 101 CALIF Us
5 102 DC Us
6 102 DC Us
7 103 NJ Us
**8 105 NY Us
9 105 NY Us
10 105 Ny Us
11 106 CALIF Us
12 107 DC Us
13 107 DC Us**


Now I have to get the output like this.

Conditions for this task is,


  • no for loop

  • hitting a database should be as minimal as possible

  • can use java or if posssible in sql query itself.



I tried with various ideas nothing worked out.

Help me out in this.

Answer

If I understand your question, these two queries will work. They use tSql window functions, if you are not using Sql Server then you will need to find the equivalent.

Edited to answer comment from OP-

If you want to create new tables:

Select * Into EmployeeNew From (
    Select  *
        From Employee E 
    Union All
    Select  Row_Number() Over (Order By eid) + (Select Max(eid) From Employee),
            ename, dept, 5 dept_code
        From Employee 
        Where Dept_Code = 1) A

Select * Into EmployeeAddressNew From (
    Select  A.*
        From Employee E
        Join EmployeeAddress A On A.eid = E.eid
    Union All
    Select  A.eaid + (Select Max(eaid) From EmployeeAddress),
            Dense_Rank() Over (Order By E.eid)  + (Select Max(eid) From Employee),
            City, Country
        From Employee E
        Join EmployeeAddress A On A.eid = E.eid
        Where E.Dept_Code = 1) A

If you only want to insert 'new' records into the original tables:

Insert Employee 
    Select  Row_Number() Over (Order By eid) + (Select Max(eid) From Employee),
            ename, dept, 5 dept_code
        From Employee 
        Where Dept_Code = 1

Insert EmployeeAddress       
    Select  A.eaid + (Select Max(eaid) From EmployeeAddress),
            Dense_Rank() Over (Order By E.eid)  + (Select Max(eid) From Employee),
            City, Country
        From Employee E
        Join EmployeeAddress A On A.eid = E.eid
        Where E.Dept_Code = 1
Comments