insert data in detail with FK (autoIncrement) from Master Table
-
Hi,
We have situation that we are clients and get Program/schedules Information from Source (Head) office that is situated in another country. Their database is different from our local database. We have two tables that we are replicating (using Transactional replication Type) so that users can avail exactly same trainings that is provided in Head office. Basically it is schedule or you can say it’s a daily time table provided by head office.
Same or slightly different table structure exists for Head office database.
1- ProgramMaster (3 faculties may have 3 different programs)
ProgramMaster may contain:
ProgramMasterId
Faculty
ProgramTitle
ProgramDate
1
PH
Physics Basic
11-Jun-07
2
CH
Chemistry I
11-Jun-07
3
MTH
Maths1
11-Jun-07
4
PH
Physics Adv.
11-Jun-07
5
CH
Chemistry II
11-Jun-07
2- ProgramScheduleDetail (every faculty my have one or more schedules per day).
ProgramScheduleDetail contains:
ProgramScheduleDetails Table:
ScheduleID
ProgramMasterId
ProgramDate
ScheduleTitle
ScheduleStartime
Duration
ScheduleEndTime
1
1
11-Jun-07
Physics at basic level1
9:30
30
10:00
2
1
11-Jun-07
Physics at basic level2
10:10
50
11:00
3
1
11-Jun-07
Physics at basic level3
11:15
15
11:30
4
2
11-Jun-07
Chemistry part I First Level
9:15
15
9:30
5
2
11-Jun-07
Chemistry part I Second Level
9:30
30
10:00
6
2
11-Jun-07
Chemistry part I Third Level
10:00
30
10:30
7
2
11-Jun-07
Chemistry part I Fourth Level
10:30
45
11:15
8
2
11-Jun-07
Chemistry part I Fifth Level
11:15
25
11:40
9
2
11-Jun-07
Chemistry part I Sixth Level
11:40
20
12:00
10
3
11-Jun-07
Math 1 basic
9:30
30
10:00
11
3
11-Jun-07
Math 2 basic
10:10
50
11:00
12
3
11-Jun-07
Math 3 basic
11:15
15
11:30
13
4
11-Jun-07
Physics Adv. I
9:15
15
9:30
14
4
11-Jun-07
Physics Adv.II
9:30
30
10:00
15
4
11-Jun-07
Physics Adv. III
10:00
30
10:30
16
4
11-Jun-07
Physics Adv. IV
10:30
45
11:15
17
5
11-Jun-07
Chemistry II Begin 1
9:15
15
9:30
18
5
11-Jun-07
Chemistry II Begin 2
9:30
30
10:00
19
5
11-Jun-07
Chemistry II Begin 3
10:00
30
10:30
20
5
11-Jun-07
Chemistry II Begin 4
10:30
45
11:15
The above data is just for providing you sample and relation/logic that I’m using here for explaining my problem.
The problem is that we adjust some schedules locally, when there is holiday in head office we have to provide timetable locally. So we keep their daily schedules in our local database in two different tables other than our existing local Schedule table.
Suppose Local Tables are called as
1- Local_ProgramMaster
2- Local_ProgramScheduleDetail
Both has auto Increment in master and detail table as explained in above tables from Head office Table structure. My query is that How can I update my Local_ Master and detail tables that is already getting changes from Head Office in above Tables. We are not Mapping with their auto Increment field, but Program Title, Faculty and Program date.
What is best solution to fix this, I there are two entries per day in master and 10 entries in detail, how can I update my Local_ tables? I want to avoid use of cursor and Trigger. Is there anyway using SELECT T-SQL? Like
Insert into Local_Master
Select mapped Columns from ProgramMaster where Faculty, proramTitle and schedule date matches ?
This will insert auto Increment IDs but how can I put their related data in details table?
Please understand my question and guide ….
Thanks in advance.
Shamshad Ali.
-
Is the real question: 'How can I make a copy of these two tables'..?
..or is there something else?
/Kenneth
-
We have already copy of Head Office schedule Tables as subscriber Tables but the problem is that, both locations has their own Identity auto Increment and the detail table is based on that auto increment ID. If any change arrises by replication they are propagated here, but how can i know and update/insert or delete those changes to my local_* tables?
I can't use
Insert into MasterTable
(select * from subscriberMasterTable where schedule_Date = GetDate())
because if there are more than one insert, how can i know which auto Increment ID would be assigned to Schedule_details Table for their related details records.
If the question is still not clear let me know.
Shamshad Ali
-
Does Faculty + ProgramTitle make a record unique?
-
Yes, that is right. Actually, I want to know how i can do it using triggers or write if Exists (with all possible cases) and use cursor to insert 1 record in masterTable, obtain scope_Identity() and then insert 1 or many related rows in DetailTable. and again get next next Faculty+programTitle and do the same MasterTable Insert again obtain scope_Identity and its detail. Until all updated. But I want to avoid use of cursor, is there any technique to use except use of Cursor? MasterTable has AutoIncrement PrimaryKey and detail depends on that Key. until i first obtain it I can't insert rows in child.
Shamshad Ali.
-
You can get ProgramMasterId from Faculty + ProgramTitle.
I don't think you need to use cursor.
-
And since the copies are just that - copies, you shouldn't have the identity (auto incerement) property in those tables either. If you remove that, and keep them as regular columns, then the problem would be less, no?
/Kenneth
-
well, ur not getting my question. The problem is that my Local_* Tables has its own autoIncrement column and based on that (unknown value) i have to insert records in child table aswell.
if two new ProgramMasterIDs are found in subscriberMasterTable.
lets say: 1193 and 1194
and it has 1193(3) and 1194(4), 3 and 4 rows in subscriberDetailsTable.
How Can i input 1193,1194 in my Local_Table, because my local_* Tables (Master and detail) contains its own autoIncrement value and based on that I have to input in 3, 4 rows in Local_details table. suppose my local_Master Table has 3015 seed value so it would be 3015 and 3016 to input these above two records. Without knowing these IDs how can I input 3015(3) and 3016(4) rows in Local_Details Table. obviously i have to use cursor and find lasted IDs from successful insert in local_Master table and assign that to its Local_Details table for relationship.
Again if i use
insert into Local_Master
select * from subscriberMasterTable where (schedule_date = GetDate() and ProgramTitle = NOT IN Local_MasterTable and Faculty NOT IN Local_MasterTable
suppose the above select returns 2 records and it inserts two rows in Local_Master with autoIncrement values 3015 and 3016, how I know these? and what about its details? how can I assign (3 related rows) to 3015 and (4 related rows) to 3016, when I have no idea how many rows select will return ???
how my problem is explained.
Shamshad Ali.
-
INSERT INTO Local_ProgramMaster (Faculty, ProgramTitle, ProgramDate)
SELECT Faculty, ProgramTitle, ProgramDate
FROM ProgramMaster
WHERE (Condition here)
INSERT INTO ProgramScheduleDetail
SELECT
(
SELECT Z.ProgramMasterId
FROM Local_ProgramMaster
WHERE Z.Faculty = (SELECT Y.Faculty FROM ProgramMaster Y WHERE Y.ProgramMasterId = A.ProgramMasterId)
AND Z.ProgramTitle = (SELECT Y.ProgramTitle FROM ProgramMaster Y WHERE Y.ProgramMasterId = A.ProgramMasterId)
),
A.ProgramDate, A.ScheduleTitle, A.ScheduleStartime, A.Duration, A.ScheduleEndTime
FROM ProgramScheduleDetail A
WHERE (Condition here)
-
I guess I'm still missing out on something...
Are you saying that you in your local (copy) tables from time to time insert your own data, that is *not* supplied from the 'master' data sent from head office?
Or are all changes made to the local based upon what data already exists in the master tables?
/Kenneth
-
Again YES, I already mentioned that we also input some schedules locally aswell and the autoIncrement is not sync with our HeadOffice Tables.
Shamshad Ali.
-
Would it be possible and feasible to insert extra columns (with NULLs allowed) into the 'local' tables? If so, then you can simply copy the content over, using the original key values in the nonidentity columns you just added. Then, you can update the local 'child' table with the correct PK values.
Note that
1. Locally entered content will leave these extra columns NULL, so selecting on those columns will ignore local entries.
2. Your code should explicitly list the fields you insert into in all cases. If your insert statements 'assume' the field order, rather than explicitly listing the field names, this won't work.
-
Thanks Steve for understanding my problem and suggestion/help.
Further, I would like to know how can I know if there is any update in Master or detail table? Again I need to have such technique to see if there is any update comes in subscriberMaster or subscriberDetail. How can i update my Local_Master and Local_Detail Tables? unless I know some changes have been made in any of subscriberTables via replication from HeadOffice?
Shamshad Ali.
-
Glad I could be of service. Regarding your further question, we have the opportunity for similar confusion. Let us say that we have record called 'A'. Would an update to 'A' replace it in its entirety, with a new record (and new PK)? or would it be a 'replace in place' and keep the same PK?
In short, from the database's perspective, how do you define the difference between a new schedule entry and an updated entry? [If there is no distinction for the update, then the answer to your question is "You can't tell that the subscriberMaster / subscriberDetail records have been updated."
-
Exactly, there is no distinction, based on data, user can change/update whole record any column coz i don't know what changes user do but not the primary key (Auto ID). I want to have something like replication. is it possible? I am putting some light triggers on subcriberTables, that will keep log of every activity (for both tables like Master_log, Detail_log) and gives some hint of data like 'Updated', 'Inserted' or 'deleted' and only in case of update i have before and after update changes in that LOG table and based on that Log I will join and udpate my local tables.
That the only solution that I only now at the moment. If there is any other way better solution that will handle my master detail case with internal Auto_ID case and map data with subscriberTables. let me know.
Shamshad Ali.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply