Any way to SQLBulkCopy insert or update if exists?
I would bulk load data into a temporary staging table, then do an upsert into the final table. See here for an example of doing an upsert.
C# Bulk Insert SQLBulkCopy - Update if Exists
Thanks to @pst
with his suggestions this is how I implemented, if anyone has to implement similar.
Bulk Insert in to permanent Temp Table
using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.BatchSize = CustomerList.Count;
bulkCopy.DestinationTableName = "dbo.tPermanentTempTable";
bulkCopy.ColumnMappings.Clear();
bulkCopy.ColumnMappings.Add("CustomerID", "CustomerID");
bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
bulkCopy.ColumnMappings.Add("LastName", "LastName");
bulkCopy.ColumnMappings.Add("Address1", "Address1");
bulkCopy.ColumnMappings.Add("Address2", "Address2");
bulkCopy.WriteToServer(CustomerList);
}
Then call a stored Procedure to Merge the temp table with actual table
using (Entities context = new Entities())
{
System.Nullable<int> iReturnValue = context.usp_Customer_BulkUploadMerge(customerid, locationID).SingleOrDefault();
if (iReturnValue.HasValue)
{
// return was successful!
}
}
This is how I used Merge in my Stored Procedure
ALTER PROCEDURE usp_Customer_BulkUploadMerge
(
@CustomerID INT ,
@locationID INT
)
AS
BEGIN
DECLARE @retValue INT
BEGIN TRY
IF OBJECT_ID('tCustomers') IS NOT NULL
BEGIN
BEGIN TRANSACTION MergPatientsTable
SET NOCOUNT ON;
MERGE dbo.tCustomers AS target
USING
( SELECT PU.CustomerID ,
PU.LocationID ,
PU.FirstName ,
PU.LastName ,
PU.MiddleInitial ,
PU.Gender ,
PU.DOB
FROM dbo.tPermanentTempTable PU
WHERE PU.CustomerID = @CustomerID
AND PU.LocationID = @locationID
GROUP BY PU.CustomerID ,
PU.LocationID ,
PU.FirstName ,
PU.LastName ,
PU.MiddleInitial ,
PU.Gender ,
PU.DOB
) AS source ( CustomerID, LocationID, FirstName,
LastName, MiddleInitial, Gender, DOB )
ON ( LOWER(target.FirstName) = LOWER(source.FirstName)
AND LOWER(target.LastName) = LOWER(source.LastName)
AND target.DOB = source.DOB
)
WHEN MATCHED
THEN
UPDATE SET
MiddleInitial = source.MiddleInitial ,
Gender = source.Gender,
LastActive = GETDATE()
WHEN NOT MATCHED
THEN
INSERT (
CustomerID ,
LocationID ,
FirstName ,
LastName ,
MiddleInitial ,
Gender ,
DOB ,
DateEntered ,
LastActive
) VALUES
( source.CustomerID ,
source.LocationID ,
source.FirstName ,
source.LastName ,
source.MiddleInitial ,
source.Gender ,
source.DOB ,
GETDATE() ,
NULL
);
DELETE PU
FROM dbo.tPermanentTempTable PU
WHERE PU.CustomerID = @CustomerID
AND PU.LocationID = @locationID
COMMIT TRANSACTION MergPatientsTable
SET @retValue = 1
SELECT @retValue
END
ELSE
BEGIN
SET @retValue = -1
SELECT @retValue
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MergPatientsTable
DECLARE @ErrorMsg VARCHAR(MAX);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SET @ErrorMsg = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
SET @retValue = 0
SELECT @retValue
-- SELECT 0 AS isSuccess
END CATCH
END
Insert new records and update existing records using SqlBulkCopy in c#
Please find below stored procedure that will insertdata if not exist and updatedata if exist on empnre01 column bases which is unique id in the table.
Create Proc usp_InsertUpdateAWSUsers
@tblUsers UsersTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
UPDATE u1
SET u1.empste01= u2.empste01
,u1.[fstnme01] = u2.[fstnme01]
,u1.[surnme01] = u2.[surnme01]
,u1.[bthcnte01]= u2.[bthcnte01]
,u1.[fnce01] = u2.[fnce01]
,u1.[lane01] = u2.[lane01]
,u1.[rsnewe01] = u2.[rsnewe01]
,u1.[sxee01] = u2.[sxee01]
,u1.[bthdate01]= u2.[bthdate01]
,u1.[nate01] = u2.[nate01]
FROM Users u1
INNER JOIN @tblUsers u2
ON u1.empnre01=u2.empnre01
INSERT INTO Users
(empnre01,empste01,[fstnme01],[surnme01],[bthcnte01],[fnce01],[lane01], [rsnewe01],[sxee01],[bthdate01],[nate01])
SELECT u2.empnre01,u2.empste01,u2.[fstnme01],u2.[surnme01],u2.[bthcnte01],u2.[fnce01],u2.[lane01],u2.[rsnewe01]
,u2.[sxee01],u2.[bthdate01],u2.[nate01]
FROM @tblUsers u2
WHERE empnre01 NOT IN (SELECT empnre01 FROM Users)
END
Then you can call this SP from C# code like this:
DataTable dt = List of data to be update in table.
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("usp_InsertUpdateAWSUsers"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@tblUsers", dt);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
res = true;
}
}
INSERT object or UPDATE if it already exists using BulkCopy (C#, SQL)
So I solved my problem (thanks #J-D).
I am basically using a temp table and inserting the data inside it. First I truncate it every time it's used:
connection.Query(database_name, sql_truncate_query);
Then I insert the data using the method I created previously:
connection.BulkInsert(database_name, ienumerable, table_name);
OBS: Changed the method name from BulkInsertOrUpdate to BulkInsert.
Later, I update my log table using a simple MERGE sql query:
INSERT [database].[table] (col1, col2)
SELECT col1, col2
FROM [database].[table2] t2
WHERE NOT EXISTS (SELECT col1 FROM [database].[table] t1 WHERE t1.col1= t2.col1);
OBS: My problem changed while I was developing, so I am no longer using the 'update' feature, I just need to check the existence of the row. Of course, you can write a code in which it updates the value if it's caught by the WHERE clause.
I don't know if it's the optimized way to resolve this problem, but it's certainly better than inserting or updating each row at a time.
Any way to check if data exist in database during an SQL bulk copy
One way to do what you're after would be to bulk-copy into a staging table (a separate table with similar layout), and then perform a conditional insert from the staging table into the real table.
You could also do something similar using a table-valued-parameter instead of SqlBulkCopy, and treat the table-valued-parameter as the staging table.
Bulk Copy or Update data from one database to another
As @Jeroen wrote in his comment, SqlBulkCopy can only insert.
However, you could insert in a temporary table and create a SQL to perform your MERGE statement in your destination table.
Disclaimer: I'm the owner of the project Bulk Operations
This library is not free but offers the BulkMerge option you are looking for.
using (BulkOperation copy = new BulkOperation(conn2))
{
copy.DestinationTableName = destinationTable;
copy.BatchSize = 1000;
copy.BatchTimeout = 240;
copy.BulkMerge(dt);
MessageBox.Show("Data successfully transfered to Central Database", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
Related Topics
How to Change the Color of Winform Datagridview Header
Prevent Textbox Autofill with Previously Entered Values
Closing a File After File.Create
Is CSV with Multi Tabs/Sheet Possible
Itextsharp - How to Get the Position of Word on a Page
Launch a Process Under Another User's Credentials
How to Set a Character at an Index in a String in C#
Entity Framework - Stored Procedure Return Value
Linq Query Group by and Selecting First Items
How to Pass Properties as "Out" or "Ref" Parameters
Selenium: Drag and Drop from File System to Webdriver
How to Write to a Onenote 2013 Page Using C# and the Onenote Interop
How to Retrieve a List of Parameters from a Stored Procedure in SQL Server