How update SQL Server table through Excel?
Hello,
for example is table:
Name Age
John 28
Mark 30
I can create Excel with the same data.
Is it possible when user change data in Excel it would updated in SQL Server Database?
For example:
John 29
I only need to update. There won't be new records. I know that possible to create SSIS package and then run JOB through Reporting Services. But i would like update directly through Excel.
October 25th, 2010 9:59am
Please check the following thread..
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/131dbe38-95a9-4b64-8434-60ba3cd6de00/
Jamie's Blog
http://consultingblogs.emc.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Let us TRY this |
My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2010 10:02am
Thanks for links. Is it not possible to update SQL Server Table without SSIS package?
October 25th, 2010 10:29am
This is SSIS forum that's why I suggested you the SSIS solution
Work around:
Then you can write Stored Procedure (or query) to do the same.
OR
use .Net to do it.
Let us TRY this |
My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2010 11:47am
If you only need update, you can try the following:
UPDATE x
SET x.age = y.age
FROM dbo.dbTable x INNER JOIN
(
SELECT [dude], [age]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=\\ps3\template\OldFarts.xls;', 'SELECT * FROM [Sheet1$]')) y
ON x.dude = y.dude
Chaos, Disorder and Panic ... my work is done here!
October 25th, 2010 9:44pm
following link might answer ur question.
http://bytes.com/topic/sql-server/answers/520213-update-sql-excel-newbie-question-challange
hope this helps
Nick
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2010 10:09pm
following link might answer ur question.
http://bytes.com/topic/sql-server/answers/520213-update-sql-excel-newbie-question-challange
hope this helps
Nick
October 25th, 2010 10:09pm
ETL vs. ELTL,
sorry, you are right. I put in the wrong section my question :)
Thank you all for help.
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2010 1:32am