stored procedure that inserts and updates a table with excel data

Question

stored procedure that inserts and updates a table with excel data

pk2dpvp

Mr or Mrs. 500

Points: 537
More actions
May 27, 2014 at 1:12 am

#307763

Hello,
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet
and so on and so on. This proces has to go through a stored procedure...
...But how? I've been searching for I don't know how many hours without any result.
I hope anyone here has a solution for me.
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

You create a SSIS package to insert data from xls to table. You can create a job to execute this SSIS package. Also you can schedule it as well as you can execute it manually.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Refer following link for the query:

http://stackoverflow.com/questions/20085249/import-excel-spreadsheet-data-to-an-existing-sql-table

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

pk2dpvp Mr or Mrs. 500 Points: 537 More actions · Answer 3

I've tried that... keep getting this error:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

David Burrows SSC Guru Points: 65138 More actions · Answer 4

You could try the ODBC Excel driver (ACE)

https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc--/

You will need to install the relevant version.

I use the 2010 redistributable on 2008R2 64 bit with OPENQUERY to access excel spreadsheets.

Far away is close at hand in the images of elsewhere.
Anon.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

+1 for SSIS

You can implement the SSIS Catalog (SSISDB) in SQL Server 2014 and execute a Package from within your own stored procedure using stored procedures provided within the SSISDB.

Deploy and Execute SSIS Packages using Stored Procedures - SQL Server 2014

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MMartin1 One Orange Chip Points: 27768 More actions · Answer 6

+1 for SSIS, and given what you said

If something already exists it should leave it, unless it's edited in the excel sheet
and so on and so on

SSIS has a Merge transform, from here you can pass this on to a conditional split to see if you want to pass the columns though to the destination , to a update/lookup, or not pass them , etc... Or it may be easier if all your data is on one place/database to use a execute sql task and use the Merge function inside it. (Though, I came upon some page describing buginess with the merge funciton , just an fyi... http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement).

----------------------------------------------------