Insert date into table from spreadsheet

Question

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

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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 · Answer 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