June 9, 2016 at 8:03 am
Hi Guys,
I have quick two quick questions.
1) I have a task where I have to read the data from a flat file and compare with Source table and destination table (Update or Insert) I am using Merge statement for this.
I am having an issue with FK relationship with my table and couple of other tables. My question is In order to keep Integrity I have to Insert the data first all others FK relational table
and then to my table, am I right? e.g
My Main table = Table_Main
FK Relationship table with Table_Main = Table_FK1
FK Relationship table with Table_Main = Table_FK2
FK Relationship table with Table_Main = Table_FK3
FK Relationship table with Table_FK2 = Table_FK2_OtherTable
FK Relationship table with Table_FK3 = Table_FK3_OtherTable1
FK Relationship table with Table_FK2_OtherTable = Table_FK2_OtherTable_OtherTable
In order for me to Successfully insert the data into My Main table I have to first insert the data into (Table_FK1,Table_FK2,Table_FK3,Table_FK2_OtherTable,Table_FK3_OtherTable1)
am I right?
2) I am sure there is a way to Enable/Disable Check Constraint. Let say If I Disable before Insert the data and then Enable it after inserting the data, it will give me an error, right?
This process will run twice a week and I want to automate this process.
Please advise.
Thank You.
June 9, 2016 at 8:13 am
You can drop and recreate foreign key constraints if you want to load data in the main table first. This can create problems with untrusted constraints. It can get very messy if the process isn't fully ACID and ends up failing after the insert but before the constraints are rebuilt or the lookup data is populated.
I don't recommend that route.
The recommended route is:
1. Populate the lookup (FK) tables
2. Populate the main table
3. Handle (log, reroute, alert) data that fails to load properly
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2016 at 8:30 am
Thank You for your prompt reply GSquared, So I have to or best practice is Pick the first Option (in my question) and 2nd and 3rd that you recommended. For this example, I can go something like this (as an example)
Insert into MyFKTable1
(Field1,Field2,Field3)
Select Field1,Field2,Field3 From SourceTable
Where FK_LinikingKey not in (Select FK_LinkingKey from MyFKTable1)
am i right?
June 9, 2016 at 8:38 am
Sounds about right.
In SSIS, you'd set it to redirect error rows.
In T-SQL, you have to write your own error-handling routine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply