Inserting values from one table into another table in SQL Server

Question

I have below table and I want to insert values into the employeenew and department table from another table (old system) for new requirement.

Employeenew` (target table)

Create table Employeenew(empo int, empname varchar(50))

Departmentnew

Create table Departmentnew(Dname varchar(50),Location varchar(50))

Currently I am using these tables in the old system:

Create table tables(id int, tableid int, tablename varchar(20))

insert into tables 
values (1, 101, 'Employee'), (2, 102, 'Department')

This table contains columns details and table id details:

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);

Below table contains entity (row) details. Each row contains an 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)

Below table contains column values for each row.

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')

Required output

I want to insert the records into Employeenew table(target table) from the table contents table of employee details(empno,ename) and insert into Departmentnew(target table) from the table contents table of department details(dname,location)

Output

Employeenew Table

EMPNO      EMPNAME
501        PAUL
502        RAJ

Departmentnew

Dname      Location
Computer   USA
Physics    INDIA   

Answer 1

   -- OLD-TABLE STRUCTURE.
   CREATE TABLE DEMO( ID INT NOT NULL, NAME VARCHAR(30), ADDRESS
   VARCHAR(30), DATE DATE );

   -- NEW-TABLE STRUCTURE.   
   CREATE TABLE DEMO_2(ID INT NOT NULL,     DATE DATE );

   -- INSERT VALUES IN OLD TABLE
   INSERT DEMO VALUES (111, 'SJ', 'BETHUDAHARI', GETDATE());
   INSERT DEMO VALUES (222, 'JS', 'KRISHNAGAR', GETDATE());

   -- CLONING DATA FROM OLD TABLE TO NEW TABLE.
   INSERT DEMO_2 
   SELECT ID, DATE FROM DEMO WHERE ID = 111;


   /* so basically you can use INSERT and SELECT statements for your task. this is 
      the simplest way, you can achieve the desired results. /*

Answer 2

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;

Answer 3

I have used Self Join here to get the requested output.

Insert into Employeenew(empo,EMPNAME)
  Select A.Value , B.value  from tablecontents A
  INNER JOIN tablecontents B ON A.entityid=B.entityid Where ISnumeric(A.value)=1 
  and ISNUMERIC(B.Value)=0;

select * from Employeenew