我有下表,我想将值employeenew
从另一个表(旧系统)插入到部门表中以满足新要求。
Employeenew`(目标表)
Create table Employeenew(empo int, empname varchar(50))
部门new
Create table Departmentnew(Dname varchar(50),Location varchar(50))
目前我在旧系统中使用这些表:
Create table tables(id int, tableid int, tablename varchar(20))
insert into tables
values (1, 101, 'Employee'), (2, 102, 'Department')
此表包含列详细信息和表 ID 详细信息:
Create table fields (id int, fieldid int, fieldname varchar(20), fieldtype varchar(100), tableid int)
insert into fields
values (1, 1001, 'empno', 'int', 101),
(2, 1002, 'empname', 'varchar(50)', 101),
(3, 1003, 'dname', 'varchar(50)', 102),
(4, 1004, 'loc', 'varchar(50)', 102);
下表包含实体(行)详细信息。每行包含一个 entityid
Create table entitylistings (id int, entityid int, tableid int)
insert into entitylistings
values (1, 10001, 101), (2, 10002, 101), (3, 10003, 102),(4, 10004, 102)
下表包含每行的列值。
Create table tablecontents(id int, fieldid int, entityid int, value varchar(max))
insert into tablecontents
values (1, 1001, 10001, 501), (2, 1002, 10001, 'PAUL'),
(3, 1001, 10002, 502), (4, 1002, 10002, 'RAJ'),
(5, 1003, 10003, 'Computer'), (6, 1004, 10003, 'usa')
(7, 1003, 10004, 'Physics'),(8, 1004, 10004, 'India')
所需的输出
我想从员工详细信息(empno,ename)的表内容表中将记录插入到Employeenew表(目标表)中,并从部门详细信息(dname,location)的表内容表中插入到Departmentnew(目标表)中
输出
Employeenew Table
EMPNO EMPNAME
501 PAUL
502 RAJ
部门new
Dname Location
Computer USA
Physics INDIA
declare @fields table(id int, fieldid int, fieldname varchar(20), fieldtype varchar(100), tableid int)
insert into @fields
values (1, 1001, 'empno', 'int', 101),
(2, 1002, 'empname', 'varchar(50)', 101),
(3, 1003, 'deptno', 'int', 102),
(4, 1004, 'dname', 'varchar(50)', 102);
declare @tablecontents table (id int, fieldid int, entityid int, value varchar(max));
insert into @tablecontents
values (1, 1001, 10001, '501'), (2, 1002, 10001, 'PAUL'),
(3, 1001, 10002, '502'), (4, 1002, 10002, 'RAJ'),
(5, 1003, 10003, '10'), (6, 1004, 10003, 'computer');
with data as
(
select f.fieldname, c.value, c.entityid
from @tablecontents c
join @fields f
on c.fieldid = f.fieldid
where f.fieldname in ('empno', 'empname')
)
select [empno], [empname]
from data d pivot (max(value) for fieldname in ([empno], [empname]))p;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句