CODEWITHSUNDEEP
c#sql.net
adrianvlupu
adrianvlupu

Reputation: 4618

Fastest way to insert rows without violating the primary key

I have a couple of tables with primary key constraints that i populate from c#.

What would be the fastest way to insert rows without violating the primary key?

Does executing statements one by one vs batch add a significant overhead?

If I would need to update certain rows based on conditions, would i be better off using a stored procedure and calling it point by point or use merge?

1.

foreach(var point in points)
{
    ...
    sqlCommand.ExecuteNonQuery(@"
    BEGIN TRY 
        INSERT INTO POINTS ... 
    END TRY 
    BEGIN CATCH 
        IF ERROR_NUMBER() <> 2627 
        BEGIN 
            THROW 
        END
    END CATCH", con)
}

2.

foreach(var point in points)
{
    ...
    sqlCommand.ExecuteNonQuery(@"
    IF NOT EXISTS (Select 1 from POINTS where ...) 
    INSERT INTO POINTS ...", con)
}

3.

StringBuilder sb = new StringBuilder();
sb.AppendLine("BEGIN TRAN");
foreach(var point in points)
{
    sb.AppendLine("IF NOT EXISTS (Select 1 from POINTS where ...) INSERT INTO POINTS ...")
}
sb.AppendLine("COMMIT TRAN");
sqlCommand.ExecuteNonQuery(sb.ToString());

4.

StringBuilder sb = new StringBuilder();
sb.AppendLine(@"
    BEGIN TRAN
    DECLARE @POINTS TABLE (
    ...
    )
");
foreach(var point in points)
{
    sb.AppendLine("INSERT INTO @POINTS ...")
}
sb.AppendLine(@"
    MERGE POINTS as T
    USING @POINTS as S
    ON T.KEY=S.KEY
    WHEN OT MATCHED THEN
    INSERT ...
");
sb.AppendLine("COMMIT TRAN");
sqlCommand.ExecuteNonQuery(sb.ToString());

Upvotes: 1

Views: 258

Answers (1)

Max
Max

Reputation: 9879

If your points collection is large, consider using SqlBulkCopy to insert it into a temp table (e.g. #NEW_POINTS) first. That way you'll avoid running an individual INSERT statement per point.

Then, run a single MERGE or a combination of INSERT/UPDATE to move the data into your actual POINTS table.

Upvotes: 7

Related Questions