如何通过.aspx页面上载Excel工作表时更新（插入）sql表中的选定列 [英] How to update(insert) a selected column in sql table when an Excel sheet is uploaded through .aspx page

问题描述

Hiii ...



我在sqlserver DB中有一个表，即EmpSalary，它包含以下列...



EmpID EmpName Jan13 Feb13 Mar13



使用C＃编程通过.aspx页面从excel表导入。



现在我想在同一个表格中插入另一张Excel表格..



但现在在将新的Excel表格插入之前表，我想删除表中的所有旧数据并插入新数据。



或



我只想检查旧数据和新数据，并将新数据中的任何新列附加到旧数据中。



如果是来自excel的新数据包括以下栏目



EmpID EmpName Jan13 Feb13 Mar13 Apr13



然后我想要将Apr13列（以及数据）附加到EmpSalary表中的旧数据。



如何使用C＃执行此操作编程。

Hiii...

I have a table in sqlserver DB namely EmpSalary and it consists of following columns..

EmpID EmpName Jan13 Feb13 Mar13

It is being imported from an excel sheet through .aspx page using C# programming.

and now i want to insert another Excel Sheet into the same table ..

But now before inserting the new Excel sheet into the table, i want to delete all the old data present in the table and insert the new data.

or

I just want to check both old data and new data and append any new column(s) in the new data to the old data..

Like if the new data from excel has the following columns

EmpID EmpName Jan13 Feb13 Mar13 Apr13

then i want to append Apr13 column(along with data) to the old data in the EmpSalary table.

How to do this using C# programming.

推荐答案

您可能需要检查首先不存在的列（如果您只想要新数据或旧数据也无关紧要）：

You probably need to check for the columns that don't exist first (doesn't matter if you only want the new data or the old data too):

IF EXISTS(SELECT column_id from sys.columns 
          WHERE Name = N'columnName' AND Object_ID = Object_ID(N'tableName'))
   BEGIN SELECT 1 END
ELSE
   BEGIN SELECT 0 END

来源 [ ^ ]



您需要将'tableName'和'columnName'替换为表的名称和相应的excel列的值。



您在excel表格中为每一列运行查询，如果它返回0，您可以添加列：

Source[^]

You need to replace the 'tableName' and 'columnName' with the name of your table and the values of your excel columns accordingly.

You run the query for each column in you excel sheet and if it returns 0 you can add the column:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 

我不知道你在excelsheet中得到了什么类型的数据，你可以尝试将值从excel映射到sql类型。只需确保列中的值适合;）



如果要删除表中的旧数据，只需调用

I don't know what type of data you got in your excelsheet you could try to map the values from the excel to an sql type. Just make sure that the values fit in the column ;)

If you want to delete the old data from the table just call a

DELETE FROM tablename

（只需删除全部旧数据）



或

(just delete all the old data)

or

TRUNCATE TABLE tablename

（这基本上从头开始重新创建表，重置索引表，自动增量值等所有内容。）





然后，您可以开始使用Excel工作表中的值添加新记录（或更新现有记录）。

(this basically recreates the table from scratch, resetting everything like index tables, auto increment values etc.)


You can then just start adding the new records (or update existing ones) with the values from your excel sheet.

这篇关于如何通过.aspx页面上载Excel工作表时更新（插入）sql表中的选定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！