Trying to avoid duplicate entries for SQL insert from a CSV file import

Question

I am trying to import CSV file data to a SQL Server Database. I got more than 15000 rows and it keeps adding new rows everyday to the CSV file. All i need is insert newly added rows to the already existing database. Problem i have right now, if i import the file its gonna insert everything including old 15000 rows. I was thinking to to insert csv data to a temporary table and filter out duplicate lines. but i dont know how to do it.

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

            }
        }

Any help would be appreciated

What don't you know how to do with the temporary table? How to create the table, how to insert records in to the table (BTW a `SqlBulkCopy` might be faster than a `SqlCommend` for uploading the data) or how to copy the new records from the temp table to the live table? — Scott Chamberlain, May 30 '18 at 15:26
I can insert data to a temporary table but i don't know how to compare it and move it to a new table. also i tried to do bulk insert but it didn't work for me, its some permission issues with the server i guess since i am using my cooperate servers i don't have fully access to everything. I am an electrical guy don't have much experience with servers. — Dinu Kuruppu, May 30 '18 at 15:32
Don't make a new real table, when you declare your temp table use a # in the front of it's name (`CREATE Table #myTempTable (Foo int, Bar nvarchar(100))`), that is a temp table that only exists for the lifetime of your connection. you should be able to bulk insert in to that temp table. As for how to compare, what information needs to be the same to be considered "duplicate", we need to know that to know how to filter out the duplicates. Here is an answer where I did this exact process https://stackoverflow.com/questions/31058886/bulk-copy-commit-rows-without-errors/31062197#31062197 — Scott Chamberlain, May 30 '18 at 15:36
Thank you very much. I think i have an idea now how to do this. — Dinu Kuruppu, May 30 '18 at 15:40
If you solve your own problem feel free to post an answer and mark it accepted. — Scott Chamberlain, May 30 '18 at 15:42

score 0 · Answer 1 · answered May 30 '18 at 22:49

You can just use IF NOT EXIST in SQL: https://forums.asp.net/t/1738957.aspx?SqlCommand+with+IF+NOT+EXISTS+statement

SQL Server Insert if not exist

So probably if you just replace your SQL command:

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

With something like this:

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

it should work.

But, as you can see in the first link it might be a good idea to create a stored procedure, because in that case you'll have a better performance.

score 0 · Accepted Answer · answered May 31 '18 at 14:35

This is how i fixed it, and so far everything working perfectly fine.

I created a procedure

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

Insert:

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

In upload click event:

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

                }

Again using a bulkinsert would be the best way to do this but i do not have permission to use it in my server. And thanks for all the help.

Trying to avoid duplicate entries for SQL insert from a CSV file import

2 Answers2