Import unique records from datatable to sql table

Question

Hi, I have a datatable matching the structure of a MS Sql table and I would like to import new records from the datatable into SQL.

So, in the example below, I am looking to import the record for Jim.

DataTable:

Name   DOB        Country
Brian  11-11-78   USA
Dave   01-01-80   UK
Jim    02-02-81   FR

SQL:

Name   DOB        Country
Brian  11-11-78   USA
Dave   01-01-80   UK

Answer 1

You can make an SP, and pass on the values that you have in your DataTable, and use MERGE (Transact-SQL) to do INSERT/UPDATE:

Pruned according to your data, assuming the name of your SQL table is _DOB:

MERGE INTO _DOB AS Target

USING  (--SELECT NAME,DOB,COUNTRY FROM FROM YOUR DataTable
        VALUES ('Jim','1978-11-11', 'FR')
       )
       AS Source (Name, DOB, Country)
ON 
    Target.Name = Source.Name
    AND Target.DOB = Source.DOB

WHEN MATCHED THEN
    UPDATE SET 
        Target.Name = Source.Name,
        Target.DOB = Source.DOB,
        Target.Country = Source.Country

WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, DOB, Country) 
    VALUES (Source.Name, Source.DOB, Source.Country);

Answer 2

INSERT INTO
    datatable (name, dob, country)
SELECT
    name, dob, country
FROM
    sql
WHERE
    NOT EXISTS(
        SELECT 
            *
        FROM
            datatable 
        WHERE
            datatable.name=sql.name AND
            datatable.dob=sql.dob
    )

Answer 3

Hi, This is what I ended up and it seems to do the trick. Thanks all for the input.

            this.conn = new  oleDbConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM name", conn);

            DataSet ds = new DataSet();

            adapter.Fill(ds, "name");

            DataTable data = ds.Tables["name"];

            sqlCommand = "CREATE TABLE ##TempTable(Name, DOB, Location)";

            SqlConnection SQLconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection1"].ConnectionString);
            SQLconn.Open();

            using (SqlCommand cmd = new SqlCommand(sqlCommand, SQLconn))
            {
                cmd.CommandType = CommandType.Text;

                cmd.ExecuteReader();

                    SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
                    // Map the columns
                    foreach (DataColumn col in data.Columns)
                    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    bulkCopy.DestinationTableName = "##TempTable";
                    bulkCopy.WriteToServer(data);

            }

            string MergeCommand = "insert into [Existing Table] (Name, DOB, Location) select distinct Name, DOB, Location from ##TempTable WHERE NOT EXISTS (SELECT 1 FROM [Existing Table] a WHERE a.[Name] = ##TempTable.[Name] and a.[DOB] = ##TempTable.[DOB])";

            SqlConnection Mergeconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
            Mergeconn.Open();

            using (SqlCommand MergeCmd = new SqlCommand(MergeCommand, Mergeconn))
            {
                MergeCmd.CommandType = CommandType.Text;
                MergeCmd.ExecuteReader();

            }

            SQLconn.Close();
            Mergeconn.Close();
        }


        }




    }