October 23, 2013 at 8:35 am
Good day all,
I am fairly new to the SQL game and would like some assistance with the following please.
Exp:
I have a list of numbers populated in a table name "addresses"
ID - NextRow
1 - 2
2 - 3
3 - 4
4 - 5
What I would like is some kind of Trigger to identify that if I should insert the list above , there isn't another starting from set of number already with the same ID range.
exp:
ID - Nextrow
3 - 4
4 - 5
I hope all of this makes sense. But if not please feel free to ask me to explain some more.
Thank you in advance
Regards
Jeff
October 23, 2013 at 9:04 am
Hi Jeff - Welcome to the forum!
For these kind of questions please supply the following:
1. Scripts to create all tables that are required
2. Script to insert sample data
3. Expected outcome of the query
Once you have provided that someone should be able to help you with this.
October 23, 2013 at 9:24 am
Welcome, and as Keith noted, more info is needed.
Your explanation isn't really clear to me either. What is the range you are avoiding, what are the edge cases and exceptions?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 24, 2013 at 1:34 am
Thank you for your responses Keith and Steve,
I am relevantly new to SQL so I will try and explain as best I can:
On the Database " Towns " , there is a Table "addresses" which consists of 3 columns.
Column "A" = ID
Column "B" = NextRow
Column "C" = RangeName (This however is not the important column)
ID is the start of the range eg. 1
NextRow is the end of that particular range eg. 2
The next number in ID would be 2 and next number in NextRow will be 3
Example :
ID | NextRow
1 | 2
2 | 3
3 | 4
4 | 5
5 | 6
then there are usually a gap between the numbers
After the gap the range will go on
Example :
ID | NextRow
6 | 7
7 | 8
8 | 9 etc.
But, just incase there are no gaps inbetween,
eg:
If there are are already a range numbered 5 - 6 in the next batch I should get a warning from sql that an overlap of numbers is occurring.
Is there any way I can get a sql code to warn me that an overlap will occur if the data should be submitted?
Unfortunately I have no formal code to use as an example. As I said I am quit new to sql and in the process of learning
Thank you again in advance
Regards
Jeff
October 25, 2013 at 4:20 am
Hi Jeff,
What I have understood is that as soon as a new row is inserted into the table with some existing values like 5 - 6 you don't want to insert the value in the table. but need a message from sql server for the same.
If "ID" is an identity field then automatically as soon a this value is added SQL server will pop up error message "Cannot insert explicit value for identity column in table 'Tablename' when IDENTITY_INSERT is set to OFF."
Still if the values are inserted after setting
SET IDENTITY_INSERT sample1 on then
You can create the Unique index on the table
create table sample1
(
ID int identity(1,1),
NextRow int
)
ALTER TABLE sample1
ADD UNIQUE (ID, NextRow)
It will make the columns "ID, NextRow" to be unique.
and as soon as duplicate values are entered you will be prompted the values like
"Violation of UNIQUE KEY constraint 'UQ__sample1__8BBCE81E6FFD3307'. Cannot insert duplicate key in object 'dbo.sample1'. The duplicate key value is (1, 2)."
Thanks,
Ammy
October 25, 2013 at 4:20 am
Hi Jeff,
What I have understood is that as soon as a new row is inserted into the table with some existing values like 5 - 6 you don't want to insert the value in the table. but need a message from sql server for the same.
If "ID" is an identity field then automatically as soon a this value is added SQL server will pop up error message "Cannot insert explicit value for identity column in table 'Tablename' when IDENTITY_INSERT is set to OFF."
Still if the values are inserted after setting
SET IDENTITY_INSERT sample1 on then
You can create the Unique index on the table
create table sample1
(
ID int identity(1,1),
NextRow int
)
ALTER TABLE sample1
ADD UNIQUE (ID, NextRow)
It will make the columns "ID, NextRow" to be unique.
and as soon as duplicate values are entered you will be prompted the values like
"Violation of UNIQUE KEY constraint 'UQ__sample1__8BBCE81E6FFD3307'. Cannot insert duplicate key in object 'dbo.sample1'. The duplicate key value is (1, 2)."
Thanks,
Ammy
October 25, 2013 at 5:13 am
Thank you so much Ammy,
Your a star!! 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply