SQL Server Performance

Load data to multiple SQL tables from a Single File

Discussion in 'General Developer Questions' started by sqlserverdeveloper, Jun 27, 2008.

  1. sqlserverdeveloper New Member

    We get a csv file every 1 hour into our FTP server and then that file needs to be grabbed and then the data from that file needs to be loaded into multiple SQL tables in a Live database which our users also will be using during that time.
    I have the following questions:
    1. Will there be any kind of table locking since it will be done to a LIVE database
    2. What's the best approach of doing this and how it can be done.
    Any ideas??
    Thanks!!

    sqlserverdeveloper, Jun 27, 2008
    #2

  2. moh_hassan20 New Member

    What's the best approach of doing this and how it can be done.
    Any ideas??
    Load the file into staging table with Table Lock ,with all checks needed
    and then insert from the staging table to your db tables.
    create SSIS package (sql 2005) or DTS(sql 2000) to implement that solution.
    Will there be any kind of table locking since it will be done to a LIVE database
    NO
    moh_hassan20, Jun 29, 2008
    #3

  3. satya Moderator

    Unless you mention any locking hints the default mechanism depends on the resources and locking requirements. By default, SQL Server will take shared row locks on inserted table and escalate to table lock if there is not enough memory for example.
    Remember that escalation always happens from row to table not row to page. In any case, this will prevent modifications from happening on the table depending on which row(s) are being touched.
    satya, Jun 29, 2008
    #4

Share This Page