CODEWITHSUNDEEP
t-sql
Anita Mathew
Anita Mathew

Reputation: 183

Select rows conditionally and insert into another table conditionally

How to insert into table 2 all field values of a row from table A, where all values in a column A in table 1 that satisfy a condition on column B of table 1 ,but do not exist in table 2.How to frame a query using not exists?

I tried this: 

INSERT INTO Teachermast (
  teacher_code,
  teacher_name,
  designation,
  dept_code,
  contact_no,
  email,
  address,
  dob,
  PASSWORD
) 
SELECT 
  userId,
  username,
  designation,
  dept,
  contact_no,
  email,
  address,
  dob,
  PASSWORD 
FROM
  UserMast 
WHERE NOT EXISTS 
  (SELECT 
    userId 
  FROM
    UserMast 
  WHERE usertype = '3')

but this doesnt seem to work. Kindly help.

Upvotes: 0

Views: 88

Answers (2)

James Casey
James Casey

Reputation: 2507

You could do a MERGE

create table users
(
    userId varchar(50),
    username varchar(50),
    usertype int,
    password varchar(50),
    contact_no varchar(50),
    email varchar(50),
    faxno varchar(50),
    address varchar(50),
    created_date date,
    updated_date date,
    status varchar(50),
    gender varchar(50),
    dob date,
    lasttimelogin datetime,
    login_time datetime,
    logoutt_time datetime,
    designation varchar(50),
    dept varchar(50),
    email_pass varchar(50)
)
insert into users values('T0003','Ankita',3,'12345','9858‌​585245','[email protected]','201','l block noid sec 25',NULL,NULL,NULL,'Female','11/09/1990',NULL,NULL,NULL,'Teacher','EC',NULL); 
insert into users values('T0004','Ribha',3,'12345','9512365423','[email protected]',NULL,'221 dwarka sec 10',NULL,NULL,NULL,'Female','12/02/1989',NULL,NULL,NULL,'Teacher','EC',NULL);

create table teachers
(
    teacher_code varchar(50),
    teacher_name varchar(50),
    designation varchar(50),
    dept_code varchar(50),
    contact_no varchar(50),
    email varchar(50),
    address varchar(50),
    dob date,
    password varchar(50)
) 
insert into teachers values('T0002','Tanvi','Teacher','CS','9632569856','tan123@gmai‌​l.com','298 mayur vihar ph 1','29/06/1990','12345');
insert into teachers values('T0003','Ankita','Teacher','EC','9858585245','anki@gmail‌​.com','201 l block noida sec 25','11/09/1990','12345');

merge teachers as target
using (select userid, username, designation, dept, contact_no, email, address, dob, password from users where usertype = 3) 
as source(userid, username, designation, dept, contact_no, email, address, dob, password)
    on target.teacher_code = source.userid
when not matched by target then
    insert (teacher_code, teacher_name, designation, dept_code, contact_no, email, address, dob, password)
    values (source.userid, source.username, source.designation, source.dept, source.contact_no, source.email, source.address, source.dob, source.password);

select * from teachers

However, I think there are issues with your database design. With your current model, you could have, for example, a different address (or password!) for Ankita in table1 compared to table2. And if you change any of that information you would have to change it in both places.

Could you, for example, just insert everyone into "users" and have "teachers" be a view 

select {columns} from users where usertype = 3

Upvotes: 1

Husen
Husen

Reputation: 1587

Please try this query

SELECT 
    t.userId,
    t.username,
    t.designation,
    t.dept,
    t.contact_no,
    t.email,
    t.address,
    t.dob,
    t.PASSWORD 
FROM Teachermast as  t
LEFT join UserMast as u on t.userId = u.teacher_code
WHERE t.usertype = '3' and u.teacher_code is null

Upvotes: 0

Related Questions