Insert date into table from spreadsheet

  • We have a table in a database where HR information is automatically imported minus the hire date. I will need to get a list of hire dates along with matching staff ids and place them in a spreadsheet to import. Would you suggest trying to import the spreadsheet into a new db in Enterprise Manager to eventually insert the hire date based on the staff id in format mm/dd/yyyy using a SQL statement? Or would an Access db saved on the server suffice?

    Would someone share an SQL statement that will insert the hire date into db1 from db2 matching on staff id (stf.id)?

  • JALB (7/1/2009)


    We have a table in a database where HR information is automatically imported minus the hire date. I will need to get a list of hire dates along with matching staff ids and place them in a spreadsheet to import. Would you suggest trying to import the spreadsheet into a new db in Enterprise Manager to eventually insert the hire date based on the staff id in format mm/dd/yyyy using a SQL statement? Or would an Access db saved on the server suffice?

    Would someone share an SQL statement that will insert the hire date into db1 from db2 matching on staff id (stf.id)?

    update db1

    set db1.hire_date = db2.hire_date

    from db1

    INNER JOIN db2 ON db1.staff_id = db2.staff_id

    Note that since you mentioned "spreadsheet", that the db2 reference in the INNER JOIN can be an OpenRowset function call directly to the spreadsheet. No need for a separate db / table to load it into first.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I would come at it an entirely different way..

    I wold built an SSIS package to handle this.

    If possible I would query the hire dates directly using a lookup dataflow component..

    my very rough thought is this:

    DataFlow

    OLEDB Source - Query does select of records that does not have a hire date, also includes staff id or other identifier.. You really only need staff id from this since you know that hire date is empty.

    Lookup component - Query includes staff id (or other identifier) and hire date, return the hire date from here.

    OLEDB Destination with access mode of SQL Query, build your update and handle the mappings.

    Second thought:

    Create table in tempdb, not a temp table but a regular table..

    Pull hire date and staff if into dataflow from some source, DB, excel, flat file, etc.

    drop data into table in tempdb

    do cross db update.. UPDATE LocalDBName.dbo.LocalTable SET HireDate = t.HireDate FROM tempdb.dbo.tempSSISTableName t INNER JOIN LocalDBName.dbo.LocalTable l ON l.StaffId = t.StaffId

    Drop table in tempdb.

    CEWII

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

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