How can I detect hits while moving something with CFrame?

There is a function that may suit your needs here, commonly used for touch detection. I present you with: :GetTouchingParts().

local touchingParts = script.Parent:GetTouchingParts()

It returns an array containing all of the parts that are touching what script.Parent is. From there, it is just a matter of looping through it.

for _,otherPart in ipairs(touchingParts) do
    print(otherPart.Name) -- If this part is found to belong to a player's character, you can damage it!
end

However for your use-case, you want to destroy the part immediately upon contact with another part. You can do this by checking if there is anything at all in the returned array.

Final script:

game:GetService("RunService").Heartbeat:Connect(function()
    local touchingParts = script.Parent:GetTouchingParts()
    if #touchingParts > 0 then
        script.Parent:Destroy()
    end
end)