Gossamer Forum
Home : General : Databases and SQL :

NEED to write a STORED PROCEDURE for the following SITUATION

Quote Reply
NEED to write a STORED PROCEDURE for the following SITUATION
Hi ,

I need to fill a TABLE called “RD_CB_VES_FACT” ( FACT Table ) with the following DIMENSION tables ( ID Field ) as hierarchy :

1) DISTRIBUTOR ( DISTRIBUTOR_ID ) , Using this DISTRIBUTOR_ID I need to Fetch the DIVISION_ID , PLANT_ID and DLY_CALENDAR_ID and under DIVISION_ID we have BRAND and inturn SERIES , similarly under PLANT , PORT and REGION . Now I need to take the ID fields from all these Dimesion tables and fill the FACT Table using a Stored Procedure .

HIERARCHY :
I)
A) DIVISION (DIVISION_ID )
B) BRAND (BRAND_ID)
C) SERIES ( SERIES_ID )

II)
A) PLANT ( PLANT_ID )
B) PORT ( PORT_ID )
C) REGION ( REGION_ID )

III)
A) DLY_CALENDAR ( DLY_CALENDAR_ID )

Please do suggest me a solution asap ... thanks in advance ..
Quote Reply
Re: [Sathish] NEED to write a STORED PROCEDURE for the following SITUATION In reply to
What I like to do is 1st write a view that will give me a visual display of the information that will be written.

I am using SQL 2005 and I do a GUI view which gives me the SQL statement for that view. I hate to write JOINS manually! I then take that SQL statement create a stored procedure and add the insert into command at the top.

If you aren't using SQL 2005 you can also try to link an Access database to your SQL database and do a query in Access to create what you need to do and get the SQL view to copy over. A little more work for this because you will need to do some code conversion once you get the SQL code from Access.