求高效批量插入sql语句写法。
需求说明：
用户表:Web_User,用户模版表：Web_UserTemplate，用户名模版明细表：Web_UserTempInfo
表结构：
Web_User {ID int,Name nvarchar(100),……}
Web_UserTemplate {ID int,UserID int,Tid int,……} 注：UserID 关联Web_User表ID字段
Web_UserTempInfo {ID int,UTempID int,……} 注：UserID 关联Web_UserTemplate表UTempID字段
业务逻辑：
DataTable table= ExcelHelper.ImportDataTableFromExcel(path, 0, 2);//导入EXCEL返回DataTable
for (int j = 0; j < table.Row.Count; j++)//所有用户信息
{
DataTable usertable = DatabaseProvider.GetInstance().GetUserSearch(user.Name, user.Sex.ToString(), user.BirthDay, "", "", "", "", "");//
if (usertable.Rows.Count > 0) //用户已经存在
{
uid = usertable.Rows[0]["ID"].ToString();//返回用户ID
}
else
{
DataTable usertableinfo = DatabaseProvider.GetInstance().InsertUserInfo(user);//添加用户
if (usertableinfo.Rows.Count > 0)
{
uid = usertableinfo.Rows[0]["ID"].ToString();
}
}
DataTable tableUserTemp = DatabaseProvider.GetInstance().GetUserTemplateSearch(uid, tid);//是否已存在模版
if (tableUserTemp.Rows.Count > 0)
{
utid = tableUserTemp.Rows[0]["ID"].ToString();//返回模版ID
}
else
{
DataTable table3 = DatabaseProvider.GetInstance().InsertUserTemplate(info);//添加新的模版
if (table3.Rows.Count > 0)
{
utid = table3.Rows[0]["ID"].ToString();返回模版ID
}
}
for (int j = 0; j < table.Columns.Count; j++)
{
DataTable tableinfo = DatabaseProvider.GetInstance().GetUserTemplateDValue(Convert.ToInt32(utid).ToString(), tid, Convert.ToInt32(table.Columns[j].ColumnName), fdate, "");
if (tableinfo.Rows.Count > 0)
{
dtSql.Add("UPDATE Web_UserTempInfo SET DValue='" + table.Rows[i][j].ToString().Replace("'",""") + "' WHERE ID=" + tableinfo.Rows[0]["ID"].ToString() + " ");
editCount++;
}
else
{
dtSql.Add("INSERT INTO Web_UserTempInfo(UTID,DID,DValue,FollowDate,Followhours,AddDate) VALUES(" + Convert.ToInt32(utid) + "," + table.Columns[j].ColumnName + ",'" + table.Rows[i][j].ToString().Replace("'", """) + "','" + fdate + "','','" + DateTime.Now.ToString() + "') ");
addCount++;
}
}
}
DbHelper.ExcuteTransactionSql(dtSql);
INSERT INTO “TABLE”SELECT 1, 'TEXT1' UNION SELECT 2, 'TEXT2' UNION SELECT 3, 'TEXT3'
