1

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

Nilesh Gajare
  • 6,302
  • 3
  • 42
  • 73
  • You can adapt the technique I've shown in my answer to [T-SQL - Insert Data into Parent and Child Tables](https://stackoverflow.com/questions/38213008/t-sql-insert-data-into-parent-and-child-tables/38217498#38217498) to insert the correct newly-created ids into the Employee table. I'm not voting to close as a duplicate because I don't want to use my sql/sql-server gold badge dupe-hammer but I think it is a good candidate for a dupe vote. – Zohar Peled Jan 10 '19 at 07:48

1 Answers1

2

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

Pham X. Bach
  • 5,284
  • 4
  • 28
  • 42