Insert date into table from spreadsheet

• JALB

  SSC Enthusiast

  Points: 142

  More actions

  July 1, 2009 at 9:33 am

  #206541

  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)?

• WayneS

  SSC Guru

  Points: 95460

  More actions

  July 1, 2009 at 10:44 am

  #1018575

  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

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  July 1, 2009 at 10:48 am

  #1018580

  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