导出表中数据,生成插入脚本

#region 生成数据表中数据的脚本
  public static  string CreateDataScript(string tableName)
  {

   bool isIdEntity=false;//此表中是否有标识列(自增)
   string commandText = String.Format("SELECT  c.name ,biaoshi=COLUMNPROPERTY( c.id,c.name,'IsIdentity') ,t.name "+
                     "FROM syscolumns c inner join  systypes t on c.xusertype=t.xusertype "+
                                 "WHERE c.ID = OBJECT_ID('{0}')",tableName);

   DataTable fieldList = DBHelper.ExecuteDataTable(CommandType.Text,commandText);
            if(fieldList ==null || fieldList.Rows.Count==0)
    return "";

   string getvalue = String.Format("SELECT * FROM [{0}]",tableName);

   DataTable dt = DBHelper.ExecuteDataTable(CommandType.Text,getvalue);
   if(dt==null || dt.Rows.Count==0)
    return "";

   string middle ="";

   for(int j=0;j<dt.Rows.Count;j++)
   {
    string fieldString="",valueString="";

    DataRow dr = dt.Rows[j];

    for(int i=0;i<fieldList.Rows.Count;i++)
    {
     string fieldName = fieldList.Rows[i][0].ToString();
     string biaoshi   = fieldList.Rows[i][1].ToString();
     
     if(biaoshi=="1")//如果此列是标识列
      isIdEntity = true;

     fieldString += "["+fieldName+"],";
     valueString += "'"+dr[fieldName]+"',";
    }
    fieldString = fieldString.Substring(0,fieldString.Length-1);
    valueString = valueString.Substring(0,valueString.Length-1);

                middle+=String.Format("\n INSERT INTO [{0}]({1}) \n VALUES({2})",tableName,fieldString,valueString);
   }

   string script = "";
   if(isIdEntity )
      script = String.Format("SET IDENTITY_INSERT [{0}] ON \n"+middle+
                           "\n\nSET IDENTITY_INSERT [{0}] OFF"+" \n Go",tableName,middle);
   else
    script = "\n"+middle;

            return script;
  }
  #endregion

转载于:https://www.cnblogs.com/meteorcui/archive/2005/11/16/2021241.html