SQL query fetching data and insert to db

Question

Table employeeAccount:

CREATE TABLE employeeAccount
( 
    employAccID NUMBER(2),
    emplyUsername VARCHAR(20),
    emplyFirstName VARCHAR(20),
    emplyLastName VARCHAR(20),
)

INSERT INTO employeeAccount(employAccID ,emplyUsername ) VALUES (1,'TestAccount1')
INSERT INTO employeeAccount(employAccID ,emplyUsername ) VALUES (2,'TestAccount2')

Table JobRole:

CREATE TABLE Jobrole
( 
    jobNo NUMBER(2),
    jobName VARCHAR(20),
)

INSERT INTO Jobrole(jobNo ,jobName) VALUES (001,'Admin')
INSERT INTO Jobrole(jobNo ,jobName) VALUES (002,'CEO')

And here is my employeeJob code:

CREATE TABLE employeeJob
(
  empid NUMBER(2),
  empjob NUMBER(2),
  CONSTRAINT pk_employeeJob PRIMARY KEY(empid,empjob),
  CONSTRAINT fk_empassignjob1 FOREIGN KEY(empid) REFERENCES employeeAccount(employAccID),
 CONSTRAINT fk_empassignjob2 FOREIGN KEY(empjob) REFERENCES Jobrole(jobNo)
)

Below is the insertion query:

INSERT INTO employeeJob(empid,empjob)
VALUES (1,'001')

Any idea how do I make it automatic take all the data like emplyFirstName, emplyLastName from employeeAccount table & insert to employeeJob. How should I do that to make it auto insert other data that existing from employeeAccount duplicate to employeeJob table?

Which DBMS are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ... — user330315
– user330315, Commented Mar 18, 2018 at 9:36
Create a procedure that receives all your data. Inside the procedure add the new job info if it doesn't exist, the new account if it doesn't exist and the new relationship between the job and the person if it doesn't exist. Then you just need to call the procedure with every account data and done! — EzLo
– EzLo, Commented Mar 18, 2018 at 10:12
i using myORA , sorry for not inserting the information to this post — thenewsboy
– thenewsboy, Commented Mar 18, 2018 at 10:22
Copying data as you ask is not how you should be manipulating data in a RDBMS. After your insert, to see the job with employee details is a query operation that joins these tables to get the information. You don’t want to have a second copy of the data. Imagine what happens if you have to follow up with an update of the name. — unleashed
– unleashed, Commented Mar 19, 2018 at 14:57

Rohan Pillai · Accepted Answer · 2018-03-18 08:51:12Z

1

Yes you can,

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

You can populate table2 automatically from table1. If you want all the records, ignore the WHERE condition. However, if you want to automatically populate only certain records from table1 to table2 write the condition in WHERE statement.

answered Mar 18, 2018 at 8:51

Rohan Pillai

9533 gold badges17 silver badges26 bronze badges

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

Comments

Cujoey · Accepted Answer · 2018-03-25 16:43:25Z

0

You can create a trigger on the employeejob table on insert like

Create trigger trgGetOtherDetails
On employeejob AFTER INSERT -- the AFTER key word is Necessary
AS
BEGIN
Declare @emplyFirstname   varchar(30), 
@emplyLastname   varchar(30),
@empId      varchar(5)

select @empId = empId from inserted

Select @emplyFirstname = emplyFirstname, @emplyLastname = emplyLastname from employeeAccount where employAccID = @empId

Update employeejob 
Set emplyFirstname = @emplyFirstname, 
emplyLastname = @emplyLastname 
Where empId = @empId

END

edited Mar 25, 2018 at 16:43

answered Mar 18, 2018 at 9:38

Cujoey

1476 bronze badges

2 Comments

thenewsboy Over a year ago

hello, thanks for the reply . it look great . but i got an error "ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword" . and other question is that "Where empId = inserted.empId" , can i change the inserted to 1 ? as I am doing fixed sql , not dynamic(insert from form)

Cujoey Over a year ago

@thenewsboy for the error you probably missed the "AFTER" key word in the query. I've Edited my code once again. this should work for you. And the "inserted" is a temporary table managed by SQL Server automatically. It stores copies of the affected rows during INSERT and UPDATE statements. So you can easily get any info of the affected row. Sorry for the late reply

Collectives™ on Stack Overflow

SQL query fetching data and insert to db

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related