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 Crowe2, John SchmidtSKILL (SkillId int, SkillName varchar (50))1, C++2, Java3, EJBEMPLOYEE_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:
- Getting the Employee identifier from EMPLOYEE and Skill identifier fromSKILL table and then using these values while inserting a record inEMPLOYEE_SKILL Table.
- Writing a single query:
INSERT INTO EMPLOYEE_SKILL (EmployeeId,SkillId) Values (Select EmployeeIdfrom Employee where Name=
