Bulk copy rows without errors

Question

I have a process that takes lists and inserts it into the database using a bulk copy of SQL due to how large this list can be. It works great, checks for constraints and all that is perfect. The problem is that if I have 10,000 records and one of these records has an error, I still want to pass the remaining 9999. Is there a way to do this otherwise than manually check each constraint before loading SQL or inserting one at a time? It seems tiring and slow what kind of lesion. Thank you

var copy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["constr"].ConnectionString, SqlBulkCopyOptions.CheckConstraints)
{ 
    DestinationTableName = obj.TableName 
};

var table = new DataTable();
copy.WriteToServer(table);

Answer 1

1 ( ) , , , , , , temp .

const string _createTableString = "Create table #temp (/* SNIP */)";
const string _insertTableString = @"
declare @sql nvarchar(2000)
set @sql = N'INSERT INTO ' + QUOTENAME(@tableName) + N' SELECT * from #temp'
exec sp_executesql @sql";

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
    connection.Open();
    using (var command = new SqlCommand(_createTableString, connection))
    {
        command.ExecuteNonQuery();
    }
    using (var copy = new SqlBulkCopy(connection))
    {
        copy.DestinationTableName = "#temp";
        copy.WriteToServer(table);
    }
    using (var command = new SqlCommand(_insertTableString, connection))
    {
        command.Parameters.AddWithValue("@tableName", obj.TableName)
        command.ExecuteNonQuery();
    }
}

QUOTENAME, , SQL- , obj.TableName.