CODEWITHSUNDEEP
c#sqlsql-serversqlbulkcopy
J. Doe
J. Doe

Reputation: 73

Skip existing records when using SqlBulkCopy

I am trying to use SqlBulkCopy to copy rows from one SQL table to another.
It works well up to the point when my destination table already contains a tuple with the primary key I am trying to add.
Now all I am looking for is an option to skip the tuple if it already exists in the destination table.
Please, can somebody hint me in the right direction?

My code so far looks like this:

using(SqlConnection source = new SqlConnection(sourceConnectionstring))
{
    source.Open();

    SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable");

    SqlDataReader reader = cmd.ExecuteReader();

    using(SqlBulkCopy bulkData = new SqlBulkCopy(destinationConnectionstring))
    {
        bulkData.DestinationTableName = "MyTable";

        bulkData.WriteToServer(reader);
    }
}

I guess one (horrible) way to do it is to create a DataTable that only contains tuples that don't exist in the destination table... But isn't there an option (or a different class maybe?) that I can use to keep it simple and quick?

Upvotes: 5

Views: 4633

Answers (2)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

But isn't there an option (or a different class maybe?) that I can use to keep it simple and quick?

No, there is no such option for the SqlBulkCopy

Disclaimer: I'm the owner of the project Bulk Operations

This library is not free but offers the InsertIfNotExists option you are looking for.

using(SqlConnection source = new SqlConnection(sourceConnectionstring))
{
    source.Open();

    SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable");

    SqlDataReader reader = cmd.ExecuteReader();

    using(BulkOperation bulkData = new BulkOperation(destinationConnectionstring))
    {
        bulkData.DestinationTableName = "MyTable";

        // INSERT only if row doesn't exist in the destination
        bulkData.InsertIfNotExists = true;
        bulkData.WriteToServer(reader);
    }
}

Upvotes: 0

Edmond Quinton
Edmond Quinton

Reputation: 1739

I don’t know of any option on the SqlBulkCopy class to skip over duplicate keys. You could try to set the IGNORE_DUP_KEY option on, on your primary key within your destination table. This will require that you have to drop your primary key constraint and re-create it as follow:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND name = N'YourPrimaryKey')
ALTER TABLE [dbo].[YourTable] DROP CONSTRAINT [YourPrimaryKey]

GO

/****** Object:  Index [PK_PKallowDup]    Script Date: 05/22/2012 10:23:13 ******/
ALTER TABLE [dbo].[YourTable] ADD  CONSTRAINT [YourPrimaryKey] PRIMARY KEY CLUSTERED 
(
    [YourPrimaryKeyColumn] ASC
)WITH (IGNORE_DUP_KEY = ON, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Upvotes: 1

Related Questions