Compare BIGINT ranges to check for overlapping

I have a table that has two BIGINT columns. The first column is for storing the beginning of a range and the second column is for storing the end of the range.

I'm trying to find a way to check to see if the BIGINT values of an INSERT statement are present within existing BIGINT ranges. If so, I want the insert statement to fail.

A simplified example of this would be this:

EXISTING DATA
col1 col2
100000 110000

NEW DATA
col1 col2
101000 101999

I don't want the new data to be inserted b/c at least one of the values contained in its range is already present in the existing data.

I've seen ways to do this with date ranges, but haven't figured out how to accomplish this with BIGINTs.

Any help would be appreciated.

Thanks,

AndrewIf you have something (a constraint or trigger) in place to make sure that col1 < col2, then you can easily check to see if there is an existing row for which both existing.col1 and new.col1 are less than both existing.col2 and new.col2

If you picture this, you will see why. If one of the col2s shuts off a range before the other col1 starts, then you don't have an overlap.

Now, since we already know that:

existing.col1 <= existing.col2
and
new.col1 <= new.col2

All we need to do to check is:

where existing.col1 <= new.col2
and new.col1 <= existing.col2

So, you want something like a trigger which does:

if exists (
select *
from inserted i
where exists (select * from myTable e where e.col1 <= i.col2 and i.col1 <= e.col2)
)
rollback transaction

Now, you will want to make sure you have a trigger on myTable which covers both col1 and col2, because you want this to run quickly. But it should work. :)

Rob|||And by the way - <= and >= will work just fine with bigints... :)|||Rob,

Thank you very much for your answer. Problem solved!

Andrew