使用SQL中修改的关系复制同一表中的数据 [英] Replicate Data in same table with modified Relations in SQL

问题描述

我们需要将数据从一个公司复制到另一个公司，为此，我们需要将一个表中存在的所有数据复制到同一表中，但使用不同的公司ID

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

表1：员工

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

表2：组合值

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

我想复制表1和表1中的所有数据。同一表中具有更新的companyid的表2

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

对于下面的表2，查询=>它按预期工作

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

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

结果

表2：组合值

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

问题：

但是对于表1，由于更新了ID，我无法执行相同操作表2中显示的部门和类别的值

预期结果
表1：员工

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

我可以使用希望避免的循环在C＃中做同样的事情，并且只对SQL查询做同样的事情

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

推荐答案

您可以使用以下命令：

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;

在上个月

这篇关于使用SQL中修改的关系复制同一表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！