Import unique records from datatable to sql table
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
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);
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 = string.Concat("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();
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
)
精彩评论