how to insert in more than one table at the same time?

Six Stars

how to insert in more than one table at the same time?

Hi!

I need to retrieve some rows from an excel sheet and put the information in two diferent tables.

In my excel sheet I have 4 columns (client, project, begining, end), so the first two columns will go to table project(id_project, fk_client,project_name...)  and the other two (begin,end) to project_duration (id_project, begin, end).

My idea was to create a view in the database to get the max ID from project to insert into the duration table.I runned the job and all data was passed to both table, but id_project didn't go to project_duration.

Can anyone help me with a better solution? Thanks!


Accepted Solutions
Employee

Re: how to insert in more than one table at the same time?

if you want to insert max ID of Project into Duration, its pretty obvious you need to load Project table first. In sub job, load duration table from view (giving you max id)

 

max_id.JPG


All Replies
Forteen Stars TRF
Forteen Stars

Re: how to insert in more than one table at the same time?

Hi,
The reason is that lookup tables or views are queried before the Excel file and before the rows are inserted in the target tables.
In this case you have to insert content into the master table 1rst, then on the next subjob you can go with the views and insert content into the child table.
Hope this helps.

TRF
Employee

Re: how to insert in more than one table at the same time?

if you want to insert max ID of Project into Duration, its pretty obvious you need to load Project table first. In sub job, load duration table from view (giving you max id)

 

max_id.JPG

Forteen Stars TRF
Forteen Stars

Re: how to insert in more than one table at the same time?

@isadoralmeida06, did it help you?

In this case thank's to mark this case as resolved (Kudos also accepted).


TRF
Six Stars

Re: how to insert in more than one table at the same time?

That's what I was doing it, like I said on the post, that's the ideia I had as shown on the PNG I attached,  but it was still putting the wrong ids. Anyways, I got it fixed. 

Thanks!