Click here to Skip to main content
15,844,387 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,

I'm newbie for .net & currently working on a project using azure sql & mvc4.

I've created tables manually on azure sql database using SSMS & have entered some values in it.

Now I've to import data from excel sheet to those tables. When I tried using "Task --> Import" wizard, it's creating a new table on azure sql database instead of updating the existing table.

What I need is: when I import from excel, the data from excel sheet should append with the existing table data's. Should not create new table on azure sql database.

It'd great helpful for me, if anyone provide solution for this. For any questions, please let me know..


1 solution

Even under ideal conditions, importing data into SQL tables is fraught with challenges unless you can guarantee that all records in the import are new. The simplest way to meet that requirement is to import into a temporary table, and feed the table to a stored procedure that checks for duplicates and either updates the existing row or discards the duplicate record, ideally accompanied by an exception report that makes its shortly into the hands of a human.

The last time I looked at DTS packages, Excel was a potential export format, but not an import format. This is fair enough, considering that you can't just point to an Excel file and say "import this," because Excel documents are complex files that look more like a multi-part MIME message than the flat files that are usually the subject of import procedures.

In the real world, an application that imports from Excel typically imports the contents of one or more of its named range. Alternatively, the named range is saved as a text file in some format, such as CSV or tab delimited, that lends itself to importing into a SQL data base. When I need to export data from Excel, I usually want only a sheet or two, or a major part thereof, from a book that contains many other sheets, which may feed data into the range of cells that are bound for the SQL data base, or may be essentially unrelated, at least the way that term is applied to a relational data base. In such cases, I usually write a short program to run through the range, and copy it into a text file. (Actually, it's simpler than that; I wrote that program long ago, as a subroutine, into which you pass the names of the source range and destination file.)

Another possibility is to have the Excel document incorporate an ODBC connection that maps the range to an updateable view of the input table, to which is attached an insert trigger that merges new records into the permanent tables.

The best approach depends largely on the needs of your application, including, among other things, what happens to the data once it gets into the SQL Server data base, and whether the workbook is a template that is populated, fed into the data base, and discarded, or a living document that is updated periodically.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900