Entering Unique data into a column

Is there a better to insure an INSERT will only add a new row only if the
data being added to a particular column is unique, besides doing a SELECT
before doing the INSERT?
SELECT X from table where X='NewData'
ExecuteReader and test for number of Rows
if Zero then do the INSERT
Thank you...
BruceINSERT INTO YourTable (x, ...)
SELECT 'NewData', ...
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE x = 'NewData')
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Thanks.....
Bruce|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Okay, What am I doing wrong? The following didn't work.. The INSERT happened
even though the value of 'Dentist' was in the table in the columne
AddressType.
INSERT INTO AddressType(AddressType) Values ('Dentist')
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
Now, my table, AddressType, had only two columns, PrimaryKey (with
autoincrement) and AddressType.
Thanks...
Bruce|||You added the VALUES clause, which means it's no longer an INSERT... SELECT.
Try:
INSERT INTO AddressType (AddressType)
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
BTW I assume AddressType is declared unique. Always declare unique columns,
don't just rely on the INSERT.
David Portas
SQL Server MVP
--|||
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:L8udnQJ32Z_Hm2ncRVn-rA@.giganews.com...
> You added the VALUES clause, which means it's no longer an INSERT...
SELECT.
> Try:
> INSERT INTO AddressType (AddressType)
> SELECT 'Dentist'
> WHERE NOT EXISTS
> (SELECT *
> FROM AddressType
> WHERE AddressType = 'Dentist')
> BTW I assume AddressType is declared unique. Always declare unique
columns,
> don't just rely on the INSERT.
> --
> David Portas
> SQL Server MVP
> --
>
This works. Thanks. And no, the column is not declared unique. I forgot
about that attribute, but will add it...
Thanks again...
Bruce