2 u011432167 u011432167 于 2016.04.12 15:02 提问

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);
        }
    }

1个回答

CSDNXIAON
CSDNXIAON   2016.04.12 15:13

SQL SERVER 批量导入时候的优化
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!