apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
TCREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:

>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
T
CREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:

>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
TCREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:
>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

Append only unique records in SQL table

I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.

The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?

Thanks in advance.
SMc

Lets say your destination table is called Dest and you temporary table is called Temp.

Both tables have two fields, A and B where A is the one that needs to be unique in Dest, but it's not necesarily unique in Temp (or Temp contains rows with a value of A that already exists in Dest).

insertinto Dest

select Temp.A, Temp.Bfrom Temp

where Temp.Anotin(select Dest.Afrom Dest)

|||

I was just fighting with similar, but a lot of simpler problem. Maybe these posts will help.

http://forums.asp.net/t/1185782.aspx

And IF NOT EXISTS(SELECT * FROM table WHERE .....

Hope it helps

Leif