BULK INSERT语句如何工作? [英] how the BULK INSERT statement work?

问题描述

嗨！

推荐答案

Google是您的朋友:很好，经常拜访他.与在这里发布问题相比，他可以更快地回答问题.

使用主题作为搜索词的快速搜索 产生了超过2 1/2百万的点击.
最受欢迎的是MSDN: http://msdn.microsoft.com/zh-cn/library/ms188365. aspx [^ ]

将来，请尝试至少自己进行基础研究，不要浪费您或我们的时间.

Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here...

A very quick search using your subject as the search term gave over 2 1/2 million hits.
The top hit is MSDN: http://msdn.microsoft.com/en-us/library/ms188365.aspx[^]

In future, please try to do at least basic research yourself, and not waste your time or ours.

来自:http://www.eggheadcafe.com/community/sql-server/13/10351461/how-to-import-excel-data-to-a-sql-table.aspx [

From: http://www.eggheadcafe.com/community/sql-server/13/10351461/how-to-import-excel-data-to-a-sql-table.aspx[^]

2. Now design a tStudent table in SQL server

Create Table 
( 
   StudentName varchar(64), 
   RollNo varchar(16), 
   Course varchar(32), 
) 

您的ms excel工作表和SQL表已经准备好了，现在该编写c#代码以将Excel工作表导入到ttStudent表中了

3.
在类文件中添加这两个名称空间

your ms excel sheet and SQL table is ready, now its time to write c# code to import the excel sheet intotStudent table

3.
Add these two name space in your class file

using System.Data.OleDb;
using System.Data.SqlClient;

使用以下代码

Use following code

public void importDataFromExcel(string excelFilePath)
{ 
   //Declare Variables - Edit these based on your particular situation
   string sSQLTable = "tDataMigrationTable";
   
   // make sure your sheet name is correct, here sheet name is Sheet1, so you can change your sheet name if have different
   string myExcelDataQuery = "Select StudentName,RollNo,Course from [Sheet1

"; 尝试 { // 创建我们的连接字符串 字符串 sExcelConnectionString = @" + excelFilePath + ;扩展属性=" + " ; 字符串 sSqlConnectionString = " ; // 执行查询以删除目标表中的所有先前数据 字符串 sClearSQL = " 删除" + sSQLTable; SqlConnection SqlConn = 新 SqlConnection(sSqlConnectionString); SqlCommand SqlCmd = 新 SqlCommand(sClearSQL，SqlConn); SqlConn.Open(); SqlCmd.ExecuteNonQuery(); SqlConn.Close(); // 一系列命令，用于将数据从excel文件批量复制到我们的SQL表中 OleDbConnection OleDbConn = 新 OleDbConnection(sExcelConnectionString); OleDbCommand OleDbCmd = 新 OleDbCommand(myExcelDataQuery，OleDbConn); OleDbConn.Open(); OleDbDataReader dr = OleDbCmd.ExecuteReader(); SqlBulkCopy bulkCopy = 新 SqlBulkCopy(sSqlConnectionString); bulkCopy.DestinationTableName = sSQLTable; while (dr.Read()) { bulkCopy.WriteToServer(dr); } OleDbConn.Close(); } 捕获(例外) { // 处理异常 } }

"; try { //Create our connection strings string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\""; string sSqlConnectionString = "SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE"; //Execute a query to erase any previous data from our destination table string sClearSQL = "DELETE FROM " + sSQLTable; SqlConnection SqlConn = new SqlConnection(sSqlConnectionString); SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn); SqlConn.Open(); SqlCmd.ExecuteNonQuery(); SqlConn.Close(); //Series of commands to bulk copy data from the excel file into our SQL table OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString); OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn); OleDbConn.Open(); OleDbDataReader dr = OleDbCmd.ExecuteReader(); SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString); bulkCopy.DestinationTableName = sSQLTable; while (dr.Read()) { bulkCopy.WriteToServer(dr); } OleDbConn.Close(); } catch (Exception ex) { //handle exception } }

这篇关于BULK INSERT语句如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！