how to avoid data redundancy in insert query?
-
hi all i am a beginner, i have written an insert stored procedure. i need my SP to check for the ID field whether the data is already present. pls help ,me. ALTER PROCEDURE [dbo].[SP_SaveGroupClassification] @SubGroupName nvarchar(50), --@GroupId int, @ParentId int, @ClassType nchar(2), @RootId int as begin INSERT INTO GroupClassification (GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) end return Here i need to check the value in (@SubGroupName not to be repeated.it is my key field. thank u
senthil
-
hi all i am a beginner, i have written an insert stored procedure. i need my SP to check for the ID field whether the data is already present. pls help ,me. ALTER PROCEDURE [dbo].[SP_SaveGroupClassification] @SubGroupName nvarchar(50), --@GroupId int, @ParentId int, @ClassType nchar(2), @RootId int as begin INSERT INTO GroupClassification (GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) end return Here i need to check the value in (@SubGroupName not to be repeated.it is my key field. thank u
senthil
Hi, what you could do is try to get the ID for the specified @SubGroupName. If the ID does not exisit, you can insert the new row.
DECLARE @ID int SET @ID = (SELECT ID FROM GroupClassification WHERE GroupName = @SubGroupName ORDER BY ID DESC) IF(@ID IS NULL) BEGIN INSERT INTO GroupClassification(GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) END -
hi all i am a beginner, i have written an insert stored procedure. i need my SP to check for the ID field whether the data is already present. pls help ,me. ALTER PROCEDURE [dbo].[SP_SaveGroupClassification] @SubGroupName nvarchar(50), --@GroupId int, @ParentId int, @ClassType nchar(2), @RootId int as begin INSERT INTO GroupClassification (GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) end return Here i need to check the value in (@SubGroupName not to be repeated.it is my key field. thank u
senthil
Use IF NOT EXISTS IF NOT EXISTS(Select a from table where x=??) INSERT INTO...
SG Cause is effect concealed. Effect is cause revealed.
-
hi all i am a beginner, i have written an insert stored procedure. i need my SP to check for the ID field whether the data is already present. pls help ,me. ALTER PROCEDURE [dbo].[SP_SaveGroupClassification] @SubGroupName nvarchar(50), --@GroupId int, @ParentId int, @ClassType nchar(2), @RootId int as begin INSERT INTO GroupClassification (GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) end return Here i need to check the value in (@SubGroupName not to be repeated.it is my key field. thank u
senthil
kssknov wrote:
Here i need to check the value in (@SubGroupName not to be repeated.it is my key field.
You should declare it as your PRIMARY KEY. As soon as you do that the database will do the work for you by ensuring that it remains unique and is not repeated.
Upcoming FREE developer events: * Developer Day Scotland My website | blog
-
hi all i am a beginner, i have written an insert stored procedure. i need my SP to check for the ID field whether the data is already present. pls help ,me. ALTER PROCEDURE [dbo].[SP_SaveGroupClassification] @SubGroupName nvarchar(50), --@GroupId int, @ParentId int, @ClassType nchar(2), @RootId int as begin INSERT INTO GroupClassification (GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) end return Here i need to check the value in (@SubGroupName not to be repeated.it is my key field. thank u
senthil
The field should either be a primary key or, or should be associated with a unique index. If a unique index exists for a field (or if it's a primary key, which is effectively a special type of unique index) then any attempt to add a record that already exists is guaranteed to fail. If two attempts are made simultaneously to add the same record, only one will succeed. In that regard, this approach is much better than the approach others here suggested of querying first to see if the record exists (two clients could both check for a record and discover it doesn't yet exist, and then both add it). There are few stylistic issues to consider: -1- If the record already exists, checking before doing the insert will avoid having a thrown exception except in the case where two clients attempt to add the same value simultaneously. Code must be prepared to handle the exception even if it checks before the insert, but avoiding the insert will probably be faster than attempting it and having it fail -2- If the record does not exist, checking the database for it will take a little time; adding the record without pre-checking would probably be faster in that case. -3- Some databases allow nulls in a unique index; some do not. Microsoft allows exactly one null to appear in (adding a second item with a null value would violate the unique index constraint). This scenario is applicable to cases where, e.g., any number of workers might have no Social Security Number, but no SSN may be shared among workers. If the database does not allow nulls in the field, one may have to store an arbitrary value instead. If there's a unique positive integer record number available, one could set the SSN field to be negative that (e.g. for record 1,234 store -1234 in the SSN). One would have to recognize and handle those numbers when viewing the data, but there would be no problem handling multiple SSN-less employees.
-
Hi, what you could do is try to get the ID for the specified @SubGroupName. If the ID does not exisit, you can insert the new row.
DECLARE @ID int SET @ID = (SELECT ID FROM GroupClassification WHERE GroupName = @SubGroupName ORDER BY ID DESC) IF(@ID IS NULL) BEGIN INSERT INTO GroupClassification(GroupName, ParentId, ClassType, RootId) VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) END -
The field should either be a primary key or, or should be associated with a unique index. If a unique index exists for a field (or if it's a primary key, which is effectively a special type of unique index) then any attempt to add a record that already exists is guaranteed to fail. If two attempts are made simultaneously to add the same record, only one will succeed. In that regard, this approach is much better than the approach others here suggested of querying first to see if the record exists (two clients could both check for a record and discover it doesn't yet exist, and then both add it). There are few stylistic issues to consider: -1- If the record already exists, checking before doing the insert will avoid having a thrown exception except in the case where two clients attempt to add the same value simultaneously. Code must be prepared to handle the exception even if it checks before the insert, but avoiding the insert will probably be faster than attempting it and having it fail -2- If the record does not exist, checking the database for it will take a little time; adding the record without pre-checking would probably be faster in that case. -3- Some databases allow nulls in a unique index; some do not. Microsoft allows exactly one null to appear in (adding a second item with a null value would violate the unique index constraint). This scenario is applicable to cases where, e.g., any number of workers might have no Social Security Number, but no SSN may be shared among workers. If the database does not allow nulls in the field, one may have to store an arbitrary value instead. If there's a unique positive integer record number available, one could set the SSN field to be negative that (e.g. for record 1,234 store -1234 in the SSN). One would have to recognize and handle those numbers when viewing the data, but there would be no problem handling multiple SSN-less employees.