Post reply

how should i handle start date and end date from my import.

  • May 15, 2008 at 12:09 pm

    #207331

    Here is my scenario.

    I am getting 6 dbf files from the customer and i am importing them using sql bulk copy to a staging database. And in the one of the Dbf files called PlanDbf. i have 2 fields called Start date and enddate which i want to capture and insert into to different tables in the production database. ( but not in the table that it is from). and i am stuck as to what to do.. The import process works where the user selects a PeriodId and then we pass that periodId to all the needed sproces and import the data in to the database..

    But now i need to eliminate the PeriodId take the start and end date from the plandbf table.

    This is my table structure

    PlanDbf.

    Plan_NUM Name1 Name2 Beg_Date End Date etc

    01212 Abc Plannme 20080101 2008313

    01211 bca jajal 20080101 2008313

    so on and so forth..

    Then in the PartDbf

    Plan_NUM PartID OP_BAL etc

    01212 XXXX 12.56

    01212 XXXX 45.45

    01211 XXXX 25.42

    so on and so forth depending on how many Plans are there in the Plandbf file.. in plan they can be N no.. of unique PartId for each plan

    SO when i am transfereing the data from the Staging databse to the production

    I want to add the BEG_Date and the End Date in the PartDbf table in the production database..

    SO suppose if my query is like this

    Select

    pd.PlanNum,

    pd.PartId

    pl.Beg_Date,

    pl.End_Date

    From

    Partdbf pd

    Inner join other tables to get the required actually. that is actally planid from a particular table

    Inner Join Plandbf pl on pd.PlanNum = pl.PlanNum.

    So i ran a test data which has around 203 records in the Plandbf and 12603 in partdbf and if i dont inner join pl in the above query the it takes 0.01 secs to exceute the whole query.. if i add the pl inner join it take around 5.17 secs..

    so i think theere should be a better way that i get that beg_date and end date in the query,,

    any help will be appreicated..

    Thanks

    Karen

  • May 15, 2008 at 12:55 pm

    #816109

    - Check your execution plans.

    Compare both execution plans (SSMS query panel (Query / Include actual execution plan (Ctrl+M) )

    - Do you have the needed indexes to support the join ?

    edited:

    on pd.PlanNum = pl.PlanNum

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 2 posts - 1 through 1 (of 1 total)

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