Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Beginner
Aug 19, 2002



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Insert Values into a Table with a Single Query

There will always be situations when you need to insert a record in a table, but the data needs to be collected from other tables present in the database itself. These insert queries replace the need for first getting the values in temporary variables and firing the new insert query. The whole insertion can be done in a single query.

Suppose in a database, you have following three tables:
EMPLOYEE (EmployeeId int, Name varchar (50))
1, David Crowe
2, John Schmidt
SKILL (SkillId int, SkillName varchar (50))
1, C++
2, Java
3, EJB
EMPLOYEE_SKILL (EmployeeId, SkillId)
1,1 : representing David Crowe has C++ skill.
1,2 : representing David Crowe has Java skill.
2,1 : representing John Schmidt has C++ skill.

To insert a new skill (already present in the SKILL table) for an employee, a record needs to be inserted into the EMPLOYEE_SKILL table. Suppose for the given data, you want to add EJB qualification for David Crowe.

It could be done in 2 ways:
  1. Getting the Employee identifier from EMPLOYEE and Skill identifier from SKILL table and then using these values while inserting a record in EMPLOYEE_SKILL Table.
  2. Writing a single query:
    INSERT INTO EMPLOYEE_SKILL (EmployeeId,SkillId) Values 
    (Select EmployeeId
    from Employee where Name=
Manish Vashistha
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date