首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >尝试避免CSV文件导入中SQL插入的重复条目

尝试避免CSV文件导入中SQL插入的重复条目
EN

Stack Overflow用户
提问于 2018-05-30 23:21:02
回答 2查看 1.2K关注 0票数 0

我正在尝试将CSV文件数据导入SQL Server数据库。我有超过15000行,它每天都在向CSV文件中添加新的行。我所需要的就是将新添加的行插入到现有的数据库中。我现在有个问题,如果我导入这个文件,它会插入所有内容,包括15000行。我正在考虑将csv数据插入到临时表中,并过滤掉重复的行。但是我不知道该怎么做。

代码语言:javascript
复制
private void Save_Import_Data_SQL(DataTable importData)
        {

            using(SqlConnection conn = new SqlConnection(myconnstring))
            {
                conn.Open();
                foreach(DataRow importRow in importData.Rows)
                {

                    DateTime Start_Date_tt = ConvertStringToDate(importRow["Start date"].ToString());
                    Decimal Start_Time_tt = ConvertStringToDecimal(importRow["Start time"].ToString());
                    DateTime Finish_Date_tt = ConvertStringToDate(importRow["Finish date"].ToString());
                    Decimal Finish_Time_tt = ConvertStringToDecimal(importRow["Finish time"].ToString());

                    Decimal Pieces_tt = ConvertStringToDecimal(importRow["Pieces"].ToString());
                    Decimal cycle_tt = ConvertStringToDecimal(importRow["Average part cycle time"].ToString());
                    Decimal length_tt = ConvertStringToDecimal(importRow["Length_pa"].ToString());


                    SqlCommand cmd = new SqlCommand("INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S) VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p)", conn);

                    cmd.Parameters.AddWithValue("@program", importRow["Program"]);
                    cmd.Parameters.AddWithValue("@group", importRow["Group"]);
                    cmd.Parameters.AddWithValue("@start_Date", Start_Date_tt);
                    cmd.Parameters.AddWithValue("@start_time", Start_Time_tt);
                    cmd.Parameters.AddWithValue("@pieces", Pieces_tt);
                    cmd.Parameters.AddWithValue("@finish_date", Finish_Date_tt);
                    cmd.Parameters.AddWithValue("@finish_time", Finish_Time_tt);
                    cmd.Parameters.AddWithValue("@avarage_part", cycle_tt);
                    cmd.Parameters.AddWithValue("@mode_p", importRow["Mode"]);
                    cmd.Parameters.AddWithValue("@length_p", length_tt );
                    cmd.ExecuteNonQuery();
                }

            }
        }

任何帮助都将不胜感激

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-05-31 22:35:43

这就是我修复它的方法,到目前为止,一切都运行得很好。

我创建了一个程序

代码语言:javascript
复制
USE [Electrical_ENG]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Silver_Robot_Insert] 
    @program varchar(50),
    @group varchar(50),
    @start_Date datetime,
    @start_time varchar(50),
    @pieces decimal,
    @finish_date datetime,
    @finish_time varchar(50),
    @avarage_part decimal,
    @mode_p varchar(50),
    @length_p decimal,
    @Total_Time_p decimal
AS

DECLARE @err_msg nvarchar(255);

IF  NOT EXISTS(SELECT * FROM Silver_Robot WHERE Program_S=@program AND Grpup_S=@group AND Start_Date_S=@start_Date AND Start_Time_S=@start_time AND Pieces_S=@pieces AND Finish_Date_S=@finish_date AND Finish_Time_S=@finish_time AND Average_Part_Cycle_Time_S=@avarage_part AND Mode_S=@mode_p AND Length_S=@length_p AND Total_Time_S=@Total_Time_p)  
BEGIN
    INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S,Total_Time_S) 
    VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p,@Total_Time_p)
END

插入:

代码语言:javascript
复制
public void Insert_Silver_Robot(String Program_S, String Grpup_S, DateTime Start_Date_S, String Start_Time_S, Decimal Pieces_S, DateTime Finish_Date_S, String Finish_Time_S, double Average_Part_Cycle_Time_S, String Mode_S, double Length_S, double Total_Time_S)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = con_str_S3;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Silver_Robot_Insert";
                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Parameters.AddWithValue("@program", Program_S);
                cmd.Parameters.AddWithValue("@group", Grpup_S);
                cmd.Parameters.AddWithValue("@start_Date", Start_Date_S);
                cmd.Parameters.AddWithValue("@start_time", Start_Time_S);
                cmd.Parameters.AddWithValue("@pieces", Pieces_S);
                cmd.Parameters.AddWithValue("@finish_date", Finish_Date_S);
                cmd.Parameters.AddWithValue("@finish_time", Finish_Time_S);
                cmd.Parameters.AddWithValue("@avarage_part", Average_Part_Cycle_Time_S);
                cmd.Parameters.AddWithValue("@mode_p", Mode_S);
                cmd.Parameters.AddWithValue("@length_p", Length_S);
                cmd.Parameters.AddWithValue("@Total_Time_p", Total_Time_S);

                  //try
                //{
                conn.Open();
                cmd.ExecuteNonQuery();
                //}catch(Exception ex)
                //{
                //    MessageBox.Show(ex.Message);
                //}
                //finally
                //{
                //    if(con.State==ConnectionState.Open)
                //    {
                conn.Close();
                //  }
                // }
            }
        }

在upload click事件中:

代码语言:javascript
复制
foreach (DataRow importRow in importData.Rows)
                {

                    DateTime Start_Date_tt = ConvertStringToDate(importRow["Start date"].ToString());
                    DateTime Finish_Date_tt = ConvertStringToDate(importRow["Finish date"].ToString());

                    Decimal Pieces_tt = ConvertStringToDecimal(importRow["Pieces"].ToString());
                    double cycle_tt = ConvertStringToDouble(importRow["Average part cycle time"].ToString());
                    double length_tt = ConvertStringToDouble(importRow["Length_pa"].ToString());
                    double total_time_tt = cycle_tt * length_tt;
                    string program_tt = importRow["Program"].ToString();
                    string group_tt = importRow["Group"].ToString();
                    string start_time_tt = importRow["Start time"].ToString();
                    string finish_time_tt = importRow["Finish time"].ToString();
                    string mode_tt = importRow["Mode"].ToString();
                    dbactions.Insert_Silver_Robot(program_tt,group_tt,Start_Date_tt,start_time_tt,Pieces_tt,Finish_Date_tt,finish_time_tt,cycle_tt, mode_tt,length_tt,total_time_tt);

                }

同样,使用bulkinsert是最好的方法,但我没有在我的服务器中使用它的权限。谢谢你的帮助。

票数 0
EN

Stack Overflow用户

发布于 2018-05-31 06:49:11

您可以只使用IF NOT EXIST in SQL:https://forums.asp.net/t/1738957.aspx?SqlCommand+with+IF+NOT+EXISTS+statement

SQL Server Insert if not exist

因此,如果您只需替换SQL命令:

代码语言:javascript
复制
SqlCommand cmd = new SqlCommand("INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S) VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p)", conn);

如下所示:

代码语言:javascript
复制
SqlCommand cmd = new SqlCommand("IF NOT EXISTS (SELECT * FROM Silver_Robot WHERE Program_S = @program AND Grpup_S = @group AND Start_Date_S = @start_Date AND Start_Time_S = @start_time AND Pieces_S = @pieces AND Finish_Date_S = @finish_date AND Finish_Time_S = @finish_time AND Average_Part_Cycle_Time_S = @avarage_part AND Mode_S = @mode_p AND Length_S = @length_p) BEGIN INSERT INTO Silver_Robot(Program_S,Grpup_S,Start_Date_S,Start_Time_S,Pieces_S,Finish_Date_S,Finish_Time_S,Average_Part_Cycle_Time_S,Mode_S,Length_S) VALUES(@program,@group,@start_Date,@start_time,@pieces,@finish_date,@finish_time,@avarage_part,@mode_p,@length_p) END", conn);

应该能行得通。

但是,正如您在第一个链接中看到的,创建一个存储过程可能是一个好主意,因为在这种情况下,您将获得更好的性能。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50608333

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档