sql server 存储过程在执行批量导入的时候由于导入的数据量太大报错,有没有优化的方法 ?

@dataset xml
AS
BEGIN
SET NOCOUNT ON;

begin tran
insert into student
    select src.a.value('./编号[1]','varchar(10)') as Id
        , src.a.value('./姓名[1]','varchar(10)') as Name
        , src.a.value('./学号[1]','varchar(10)') as No
        , src.a.value('./学科[1]','varchar(10)') as Class
        , src.a.value('./分数[1]','varchar(20)') as Score
        , src.a.value('./年龄[1]','varchar(30)') as Age
        , src.a.value('./性别[1]','bit') as Sex
        , src.a.value('./TOP[1]','bit') as TOPFlag
        , src.a.value('./KA[1]','bit') as KAFlag
     from @dataset.nodes('//NewDataSet/Table') as src(a) 
commit tran

@dataset 通过.net 提交:

 public static void Import(DataSet import)
    {
        using (System.IO.StringWriter writer = new System.IO.StringWriter())
        {
            import.WriteXml(writer);

            Database db = CommonDAL.GetDatabase();

            DbCommand cmd = db.GetStoredProcCommand("P_Import");
            cmd.CommandTimeout = 0;
            db.AddInParameter(cmd, "dataset", DbType.Xml, writer.ToString());

            db.ExecuteNonQuery(cmd);
        }
    }
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐