是否有任何可用于将excel导入现有Azure sql表的规定？ [英] Is there any provision available to import excel into existing Azure sql table?

问题描述

大家好，



我是.net&的新手目前正在使用azure sql& amp; mvc4。



我使用SSMS& amp ;;我在azure sql数据库上手动创建了表格。已经输入了一些值。



现在我要将excel表中的数据导入到这些表中。当我尝试使用任务 - >导入向导时，它会在azure sql数据库上创建一个新表而不是更新现有表。



我需要的是：当我从excel导入时，excel表中的数据应附加现有的表数据。不应该在azure sql数据库上创建新表。



如果有人为此提供解决方案，它对我很有帮助。如有任何问题，请告诉我..



谢谢，

Arun

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..

Thanks,
Arun

推荐答案

即使在理想条件下，将数据导入SQL表也会遇到挑战，除非您可以保证导入中的所有记录都是新的。满足该要求的最简单方法是导入临时表，并将表提供给存储过程，该存储过程检查重复项并更新现有行或丢弃重复记录，理想情况下附带一个异常报告，使其很快进入人类之手。



我最后一次查看DTS包时，Excel是一种潜在的导出格式，但不是导入格式。这是公平的，考虑到你不能只指向一个Excel文件并说导入这个，因为Excel文档是一个复杂的文件，看起来更像是一个多部分的MIME消息而不是平面文件通常是导入程序。



在现实世界中，从Excel导入的应用程序通常会导入其一个或多个命名范围的内容。或者，命名范围以某种格式保存为文本文件，例如CSV或制表符分隔，这样可以导入到SQL数据库中。当我需要从Excel导出数据时，我通常只需要包含许多其他工作表的工作簿中的一张或两张或其中的一部分，这些工作表可能会将数据提供给绑定到SQL数据库的单元格范围内，或者可能基本上不相关，至少是该术语应用于关系数据库的方式。在这种情况下，我通常编写一个简短的程序来运行该范围，并将其复制到文本文件中。 （实际上，它比那更简单;我很久以前编写了这个程序，作为子程序，你传递了源范围和目标文件的名称。）



另一种可能性是让Excel文档包含一个ODBC连接，该连接将范围映射到输入表的可更新视图，附加了一个插入触发器，将新记录合并到永久表中。



最好的方法在很大程度上取决于应用程序的需求，其中包括数据进入SQL Server数据库后会发生什么，以及工作簿是否是填充的模板，输入数据库，丢弃，或定期更新的活文档。

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.

这篇关于是否有任何可用于将excel导入现有Azure sql表的规定？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！