Order to insert / update data on tables (FK)

Question

Order to insert / update data on tables (FK)

river1

SSC-Insane

Points: 24309
More actions
May 11, 2016 at 1:16 am

#316443

Dear all,
I have this tables below:
bmb_ms.operator
bmb_ms.asset_placement
bmb_ms.audit_result
bmb_ms.bmb_user
bmb_ms.cost_type
bmb_ms.cup_sale
bmb_ms.ingredient
bmb_ms.ingredient_consumption
bmb_ms.kpi
bmb_ms.kpi_impact
bmb_ms.machine_kpi_counter_status_alarm
bmb_ms.machine_status
bmb_ms.material_bmb
bmb_ms.material_sale
bmb_ms.poc_team
bmb_ms.request_definition
bmb_ms.spare_part
bmb_ms.team
bmb_ms.ticket
bmb_ms.ticket_status
bmb_ms.ticket_type
bmb_ms.work_order_cost_item
bmb_ms.work_order_type
bmb_ms.estimated_sale
bmb_ms.machine_bods
bmb_ms.machine_park
bmb_ms.machine_park_monthly_snapshot
bmb_ms.poc
bmb_ms.poc_group
bmb_ms.poc_group_poc
bmb_ms.poc_responsible
bmb_ms.poc_under_responsibility
bmb_ms.serving_recipe
I asked a team to create a ETL process to feel this tables. Their are asking what is the order in which data should be loaded as there are a lot of tables with FKs
Can you advice on a script that can show the order on which tables should be loaded?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

FTdenali SSCertifiable Points: 5362 More actions · Answer 1

Hi

If this tables are linked to an application or applications, the application process flow should guide you on what gets captured first. So perhaps if you enable a profiler and run a single transaction from the application front-end. then based on the results of the profiler you can detect which tables get populated first. If that is not helpful, then you need to study the business process first because some of these tables may have business keys that act as primary keys.

river1 SSC-Insane Points: 24309 More actions · Answer 2

The order that I want is just related with fk. No business need

FTdenali SSCertifiable Points: 5362 More actions · Answer 3

the order of etl should then be in sequence with the foreign keys. by comparing it's key to the table which holds the same key as primary key.

river1 SSC-Insane Points: 24309 More actions · Answer 4

What they want is an excel with the tables sequence. Does anyone have a script capable of doing this?

Lowell SSC Guru Points: 323518 More actions · Answer 5

there is a built in stored procedure from Ms to get the objects in FK hierarchy order.

If you have a circular reference, you can get stuck in a loop(A references B which references C which FK's back to A again, that kind of thing)

EXEC sp_msdependencies @intrans = 1

.

you'll need to filter out the objects and object types you do not want.

SET NOCOUNT ON

CREATE TABLE #MyObjectHierarchy

(

HID int identity(1,1) not null primary key,

ObjectID int,

SchemaName varchar(255),

ObjectName varchar(255),

ObjectType varchar(255),

oTYPE int,

SequenceOrder int

)

--our list of objects in dependancy order

INSERT #MyObjectHierarchy (oTYPE,ObjectName,SchemaName,SequenceOrder)

EXEC sp_msdependencies @intrans = 1

UPDATE MyTarget

SET MyTarget.objectID = objz.object_id,

MyTarget.ObjectType = objz.type_desc

FROM #MyObjectHierarchy MyTarget

INNER JOIN sys.objects objz

ON MyTarget.ObjectName = objz.name

AND MyTarget.SchemaName = schema_name(objz.schema_id)

SELECT * FROM #MyObjectHierarchy ORDER BY HID --order for insert of data/CREATION OF TABLES

SELECT * FROM #MyObjectHierarchy ORDER BY HID desc --order for DELETE of data/ drop OF TABLES

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!