Update SQL From excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update SQL From excel

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-23 : 11:06:20

Hey guys
Hope your well
Not sure if i am in the correct forum or not
Aim- update a table in SQL from excel
Sql Table : [FDMS].[dbo].[Master_RM_Account_List]
Fields:[RM_Key]
,[MerchantID]
,[RMID]
,[Include]
,[RM_Added]
,[Start_Date]
,[End_Date]
,[RM_Name]
,[Area]
,[Active]

Excel
File Name : RM Assignment
Fields:ParentID – Column A
,Rm_Account –Column D

Mapping
Sql Table = Excel Table
MerchantID = ParentID
RMID = Rm_Account

When ParentId is assigned a Rm_account code (in the excel document), i want to populated SQL table (field :RM_Added) with the last day of the given month. For eg if a parrentid was assigned today 23/10/13 i want the date format to be 2013-10-31. I want the Start_Date Populated with the begining of the month eg ‘2013-05-01’ (This calculation needs to be from the date the RM_account was assigned.

I need this to be via a windows authentation
I am hoping you can help

Regards
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:16:33

you can use OPENROWSET for that
something like


UPDATE t
SET RM_Added = DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,-1),
Start_date = your logic based on RM_account
FROM [FDMS].[dbo].[Master_RM_Account_List] m
JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=<Full path>\FileName.xls', [SheetName$])xl
ON xl.ParentID = m.MerchantID 
AND xl.Rm_Account = m.RMID

I'm assuming its a xls file with column header information
ALso didnt understand on Start_date calculation logic so make sure you put correct logic in blue part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-24 : 04:10:28

Hi Visakh16

Sorry to ask such a question

But the code you provide, where do i put that ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 08:25:06

You can run it directly in SQL Server management studio. Or place it inside a procedure and use EXEC Procedurename in SSMS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources