check the key value before inserting data [modified]---ALREADY SOLVED---

[Mycroft Holmes]

I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.

Never underestimate the power of human stupidity RAH

[J4amieC]

PIEBALDconsult wrote:

If there's something that needs to be unique, then it should be created/set by the system not the user

So next time you signup for an online service your username will be papsd9308skjfd9kdi32.

[Lost User]

I disagree, on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it.

Bastard Programmer from Hell :suss:

[Lost User]

Take a list of usernames. Usernames should be unique, but there's usually an identity. The identity is for the system, the username for the user. Same goes for a filename; they need be unique, but you don't let the system choose it.

Bastard Programmer from Hell :suss:

[PIEBALDconsult]

Eddy Vluggen wrote:

filename; they need be unique

Not necessarily.

[PIEBALDconsult]

Yeah, pretty much, at least I won't have to waste a half hour trying things and trying things always having it tell me it exists. Think of Animal House -- "Your name is Flounder. Your name is Pinto." Simple, saves the user a lot of needless trouble.

[Lost User]

Your new contract is located on the Z-share, and is called "contract.doc". Aw, there are two more files with the same name in there. :z

Bastard Programmer from Hell :suss:

[Chris Meech]

Again depending upon the database and how the constraint operation is implemented, there may also be performance gains by using the constraint instead of coding it into a program. But your mileage may vary extensively. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

[Mycroft Holmes]

Eddy Vluggen wrote:

on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it

If I am coding file I/O I certainly do!

Never underestimate the power of human stupidity RAH

[PIEBALDconsult]

That wouldn't be in the database, would it?

[Lost User]

That wouldn't matter, would it? :) Some people generally check every option, others trust in a form of exception-handling.

Bastard Programmer from Hell :suss:

[PIEBALDconsult]

Yes, it would.

[Lost User]

Why does it matter to you? Do you make a difference between deleting a file from disk and a file from a database, from an exception-handling viewpoint? :) It's a personal preference; lots of experienced programmers tend to check specific whether a file (or record) exists and whether they've got the appropriate rights to delete it (from disk or database). I prefer to rely on exception-handling, has always worked quite well for me. Do you try-except, or do you return a customized boolean that consists of Yes, No and EFileNotFound?

Bastard Programmer from Hell :suss:

[PIEBALDconsult]

I'm pretty sure none of that has anything to do with the original question.

Eddy Vluggen wrote:

a difference between deleting a file from disk and a file from a database

Yes. The database will likely return "0 records affected". The file system may simply say "ok, it's gone" even if it didn't exist. Either way, I don't care, just aslong as it's gone.

Eddy Vluggen wrote:

whether they've got the appropriate rights to delete it

That's a case where looking first may give inacurate information and you could wind up having to handle an Exception anyway, so I prefer to just try it and see.

[Lost User]

PIEBALDconsult wrote:

I'm pretty sure none of that has anything to do with the original question.

True.

Bastard Programmer from Hell :suss:

[Niladri_Biswas]

Hello SayamiSuchi, This problem can be solved in many ways. Method 1: As has already been answered by UNCRushFan

IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END

Method 2: Another traditional way [ Count(ColumnName) approach ]

Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
--Select * from @tblSource

Declare @key as varchar(10)
set @key = 'Joy2'

if(( Select count([KEY]) from @tblSource where [key] = @key) = 1)
begin
insert into @tblSource Select 'NewName', 'NewKey'
end

Select * from @tblSource

Method 3: Merge statement of Sql Server 2008 Consider the below statement first

Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'

Declare @tblDestination table([ID] int identity,[Name] varchar(20),[Key] varchar(10))

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);

Select * from @tblDestination

I have a source table and a destination table. I am inserting the record in the destination table by checking whether the Key of the source table exists in the destination or not. In this case no key will be found in the destination and hence the output will be

ID Name Key
1 Sam Sam1
2 Joy Joy2

Now let us insert a record (with an already existing key) in the source table as

insert into @tblSource Select 'Joy','Joy2'

At this time the records in the source table will be

ID Name Key
1 Sam Sam1
2 Joy Joy2
3 Joy Joy2

Now if we run the above Merge statement query

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination

The output in the destination table will be

ID Name Key
1 Sam Sam1
2 Joy Joy2

because the key is already present in the destination table and hence it has been ignored. But if we have the source table as

insert into @tblSource Select 'NewName',