CODEWITHSUNDEEP
sqlsql-serverdatabase
Nilesh Gajare
Nilesh Gajare

Reputation: 6398

Replicate Data in same table with modified Relations in SQL

We had a requirement to copy data from one company to another, for this we need to replicate all the data present in one table into the same table but with a different company id

Table 1: Employee

FName      Id   Department  CatId  CompanyId
Pratik      1    1            4       1
Praveen     2    2            3       1
Nilesh      3    2            3       1

Table 2 : ComboValues

Id   Fieldname  FieldValue  CompanyId
1    Department     IT        1
2    Department     HR        1
3    Category       Staff     1
4    Category       Manager   1

I want to replicate all the data present in table 1 & table 2 in the same table with Updated companyid

For Table2 below is the query => It is working as Expected

INSERT INTO ComboValues (Fieldname,FieldValue, CompanyId)
(SELECT Fieldname,FieldValue,2 WHERE Companyid = 1)

Result

Table 2 : ComboValues

Id   Fieldname  FieldValue  CompanyId
1    Department     IT        1
2    Department     HR        1
3    Category       Staff     1
4    Category       Manager   1
5    Department     IT        2
6    Department     HR        2
7    Category       Staff     2
8    Category       Manager   2

Problem :

But for Table1 I'm not able to do the same because of updated ID values of Department and Category present in table 2

Expected Result Table 1: Employee

FName      Id   Department  CatId  CompanyId
Pratik      1    1            4       1
Praveen     2    2            3       1
Nilesh      3    2            3       1
Pratik      4    5            8       2
Praveen     5    6            7       2
Nilesh      6    6            7       2

I can do the same in C# by using loops which I want to avoid and do same with SQL query only

Upvotes: 1

Views: 296

Answers (1)

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this:

WITH tmp_table AS
(
    SELECT o.id AS id_old, n.id AS id_new
    FROM combovalues o
    INNER JOIN combovalues n 
    ON o.fieldname = n.fieldname AND o.fieldvalue = n.fieldvalue
    WHERE o.companyid = 1 AND n.companyid = 2
)
INSERT INTO employee (fname, department, catid, companyid)
SELECT fname, d.id_new, c.id_new, 2
FROM employee e
LEFT JOIN tmp_table d
ON e.department = d.id_old
LEFT JOIN tmp_table c
ON e.catid = c.id_old
WHERE companyid = 1;

Tested in rextester

Upvotes: 2

Related Questions