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

I can't see anywhere in your sample data that links '501' with 'Paul' or '502' with 'Raj'... How is that derived? — ZLK
– ZLK, Commented Oct 29, 2018 at 3:47
sorry.i made small changes in my orginal post.entityid(10001) will be same for 501 with 'paul' and entityid(10002) will be same for 502 with 'raj' in entitylistings table and tablecontents table and it is unique for each row and tableid is 101 for employee. — Ram
– Ram, Commented Oct 29, 2018 at 5:22

sepupic · Accepted Answer · 2018-10-29 08:46:43Z

1

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;

answered Oct 29, 2018 at 8:46

sepupic

8,7371 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ram Over a year ago

Now i am trying to work for any tables with input parameter as Tableid,so i used tabeid in where condition(CTE QUERY).But not able to select field names for this corresponding tableid in outer query.In above query u specified the field names([Empno],[Empname]) in outer query(data d).instead of sepcifying field names in outer query,shall we select fieldnames from field table for the corresponding tableid.?

Ram Over a year ago

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 (select name from fields where tableid=101) ) select [empno], [empname] from data d pivot (max(value) for fieldname in ([empno], [empname]))p;

Ram Over a year ago

Thank you very much.It is working fine for me.I derived one query using your query to work for any table using dynamic sql.

Amin Mozhgani · Accepted Answer · 2018-10-29 08:13:33Z

1

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

edited Oct 29, 2018 at 8:13

Amin Mozhgani

6041 gold badge7 silver badges22 bronze badges

answered Oct 29, 2018 at 6:02

Chitra Arumugam

112 bronze badges

3 Comments

Ram Over a year ago

Thanks and it is working for Employee table.But i tried another one table having first and second column having string values it is not coming correctly. is it possible to form generic query using tableid column in where condition.

Chitra Arumugam Over a year ago

Share the sample data... So that i'll share you the query

Ram Over a year ago

Now i made changes in my original post.Now i am trying to insert into Departmentnew(target table) from the table contents table of department details(dname,location)

Papai from BEKOAIL · Accepted Answer · 2018-10-29 09:26:14Z

0

   -- 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. /*

answered Oct 29, 2018 at 9:26

Papai from BEKOAIL

1,5391 gold badge13 silver badges18 bronze badges

Collectives™ on Stack Overflow

Inserting values from one table into another table in SQL Server

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related