>问题
如何编写代码导入数据表到SQL Server 2012 .
详
我有 excel 工作表名称发票数据.xlsx有两列
UnitCode CurrentMeterReading
21544 2900
22152 9000
19822 9200
我从 excel 工作表到数据表获取成功数据如下:
public System.Data. DataTable Showdataprint()
{
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string str = @"SELECT [UnitCode],[CurrentMeterReading] FROM [Sheet1$]";
OleDbCommand com = new OleDbCommand();
com = new OleDbCommand(str, con);
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds,"[Sheet1$]");
con.Close();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["[Sheet1$]"];
return dt;
}
我成功地从excel到数据表获取数据现在我需要将数据从数据表导入到 sql 服务器 2012表名称 : 华氏发票PK : 串行
CREATE TABLE [dbo].[WAHInvoice](
[Serial] [int] NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[UnitCode] [int] NULL,
[CurrentReadingDate] [date] NULL,
[CurrentMeterReading] [decimal](18, 2) NULL,
CONSTRAINT [PK_WAHInvoice_1] PRIMARY KEY CLUSTERED
(
[Serial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
我需要知道如何将数据从 excel 导入到 sql .
假设我在表 WahInvoice 中有 sql 的数据
对于 Max Series 2000,这意味着数据如下:
Serial UnitCode CurrentMeterReading
2001 21544 2900
2002 22152 9000
2003 19822 9200
其余列的所有值都将为 null 。
实际上我在导入按钮下为将数据导入sql编写的内容在导入按钮下单击我写如下:
System.Data.DataTable dt = new System.Data.DataTable();
dt = Showdataprint();
//How to import datatable to sql server 2012
您是否正在寻找 SqlBulkCopy 方法?如果是,请检查以下内容。
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110(.aspx
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server