SQL删除重复数据

原创

Johnny_Cheung 2023-02-25 16:14:23 博主文章分类：sql server ©著作权

©著作权归作者所有：来自51CTO博客作者Johnny_Cheung的原创作品，请联系作者获取转载授权，否则将追究法律责任

<span style="font-size:14px;"> /// <summary>
        /// DataTale整张表数据插入数据
        /// </summary>
        /// <param name="dt">要插入的table数据</param>
        /// <param name="tableName">目标数据表名</param>
        /// <param name="fieldName">必须提供所有的字段</param>
        /// <returns>返回成功，或者失败 true  or false</returns>
        public static bool SqlBulkInsertforLeads(DataTable dt, string tableName, string[] fieldName)
        {
            using (SqlConnection conn = new SqlConnection(Conn.ConnString))
            {
                conn.Open();
                using (SqlBulkCopy bulk = new SqlBulkCopy(Conn.ConnString))
                {
                    try
                    {
                        //when the table data handle done
                        bulk.DestinationTableName = tableName;
                        for (int i = 0; i < fieldName.Length; i++)
                        {
                            bulk.ColumnMappings.Add(fieldName[i], fieldName[i]);
                        }
                        bulk.WriteToServer(dt);


                        //clear the table repeat data
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            StringBuilder temp = new StringBuilder();
                            temp.AppendFormat(" DELETE FROM {0} where CategoryID={1} AND ID IN (( ", tableName, Convert.ToInt32(dt.Rows[0][1]));
                            temp.AppendFormat(" SELECT ID FROM (select MAX(ID) AS ID,Word,CategoryID from {0} group by Word,CategoryID having COUNT(Word)>1) AS A))", tableName);
                            cmd.CommandText = temp.ToString();
                            cmd.ExecuteNonQuery();
                        }
                        return true;
                    }
                    catch
                    {
                        return false;
                    }
                    finally
                    {
                        conn.Close();
                        bulk.Close();
                    }
                }
            }
        }</span>