CODEWITHSUNDEEP
c#asp.net.net
Rahul
Rahul

Reputation: 166

SqlBulkCopy Ignore Duplicate Records of Datatable From DataBase

Can i ignore the duplicate records of a data already present in sql database from a datatable which i am passing to SqlBulkCopy. If Yes then How and also explain me if No and other option.

Upvotes: 3

Views: 4211

Answers (3)

Palif
Palif

Reputation: 1

Another way to do it is to create a database trigger to replace the inserts initiated by SqlBulkCopy. The performance will be impeded, depending on, among other things, the size of the batch, but it works nonetheless.

CREATE TABLE [dbo].[TempTable] (
    [Id] INT IDENTITY PRIMARY KEY,
    [Val] NVARCHAR(20)
)

GO

CREATE OR ALTER TRIGGER [IgnoreDuplicates] ON [dbo].[TempTable] 
INSTEAD OF INSERT 
AS 
BEGIN
    SET NOCOUNT ON
    INSERT INTO [dbo].[TempTable]([Val])
    SELECT [Val] FROM [INSERTED] WHERE [Val] NOT IN (
        SELECT [Val] FROM [dbo].[TempTable]
    )
END

GO

Upvotes: 0

GinjaNinja
GinjaNinja

Reputation: 806

As previous poster said, this is not built in. I achieve similar using the following:

SQL Stored Procedure that accepts a TableValuedParameter with the data you require.

In the stored proc, I then INSERT all records into a temp table. Once you have it there, you can use SQLs MERGE statement in your stored proc to insert data where it doesn't already exist.

So, let us assume that our data is simply people's names stored in a table people. We hold only an ID and a name. I also assume this table is called 'people'.

Here's how I create my Table Valued Parameter type (created in SQL Server)

CREATE TYPE udt_person AS TABLE(
[id] [INT] NOT NULL,
[name] [nvarchar(50)] NULL
)
GO

I now create the stored procedure:

CREATE PROCEDURE SaveNewPeople @pPeople udt_Person
AS
BEGIN
    -- Create Temp table
    CREATE TABLE #tmpPeople (id INT, name VARCHAR 50)

    -- We will stage all data passed in into temp table
    INSERT INTO #tmpPeople
    SELECT id, name FROM @pPeople

    -- NB: you will need to think about locking strategy a bit here
    MERGE people AS p
    USING #tmpPeople AS t
    ON p.id = t.id
    WHEN NOT MATCHED BY TARGET THEN
        -- We want to insert new person
        INSERT (id, name) VALUES (t.id, t.name)
    WHEN MATCHED THEN
        -- you may not need this, assume updating name for example
        UPDATE SET p.name = t.name

END

Now we have the SQL in place.

Let us create the bulk of data in C#:

DataTable ppl = new DataTable();
ppl.Columns.Add("id", typeof(int));
ppl.Columns.Add("name", typeof(string));

// table is created, let's add some people
var bob = ppl.NewRow();
bob["id"] = 1;
bob["name"] = "Bob";
ppl.Rows.Add(bob);

var jim = ppl.NewRow();
jim["id"] = 2;
jim["name"] = "Jim";
ppl.Rows.Add(jim);

// that's enough people for now, let's call the stored procedure
using(var conn = new SqlConnection("YouConnStringHere"))
{
    using(var cmd = new SqlCommand("SaveNewPeople", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        var tvp = new SqlParameter
        {
          ParameterName = "@pPeople",
          SqlDbType = SqlDbType.Structured,
          Value = ppl,
          TypeName = "udt_person"
        }
        cmd.Parameters.Add(tvp);
        conn.Open();
        cmd.ExecuteNonQuery();
    }

}

Hopefully this gives you the idea. If you then modified the C# datatable, you should see rows inserted, updated or ignored.

Good luck.

Upvotes: 2

usr
usr

Reputation: 171206

No, that's not built-in. You need to clean the data on the client or insert into a staging table first.

Upvotes: 2

Related Questions