Returning values from unique index

I have a table1(key, field1, field2, field3...).
My primary key is "key".
Then i create a unique index for fields "field1" and "field2".
This works, but i want that sql server retrieve me the values thar are being
duplicated.
I'm using sql server 7.0.
Ex: Table1 as the following values:
-0;0;0;0;
-1;1;1;1;
-2;2;2;2;
I have a second table with the values:
-3;3;3;3;
-4;1;1;1;
-5;0;0;0;
-6;0;0;0;
if i do a insert (insert into table1 select * from table2), it gives me an
error but i want that sql tell me that the error was because of values
4;1;1;1-5;0;0;0-6;0;0;0.
I hope this helps.
Can anyone help me?What was the error you got?
Madhivanan|||SQL Server won't identify the duplicated rows automatically for you.
You'll have to do a separate query:
SELECT T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
You could also insert only the unique rows as follows:
INSERT INTO Table1 (col1, col2, cole, col4)
SELECT T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
LEFT JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
WHERE T1.col1 IS NULL
David Portas
SQL Server MVP
--|||Hi David,

INSERT INTO Table1 (col1, col2, cole, col4)
SELECT T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
LEFT JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
WHERE T1.col1 IS NULL <<
I think that a DISTINCT should be added to the SELECT list.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111143246.610107.236070@.g14g2000cwa.googlegroups.com...
> SQL Server won't identify the duplicated rows automatically for you.
> You'll have to do a separate query:
> SELECT T2.col1, T2.col2, T2.col3, T2.col4
> FROM Table2 AS T2
> JOIN Table1 AS T1
> ON T1.col1 = T2.col1
> AND T1.col2 = T2.col2
> You could also insert only the unique rows as follows:
> INSERT INTO Table1 (col1, col2, cole, col4)
> SELECT T2.col1, T2.col2, T2.col3, T2.col4
> FROM Table2 AS T2
> LEFT JOIN Table1 AS T1
> ON T1.col1 = T2.col1
> AND T1.col2 = T2.col2
> WHERE T1.col1 IS NULL
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 18 Mar 2005 10:26:40 -0000, Joaquim Meireles wrote:

>I have a table1(key, field1, field2, field3...).
>My primary key is "key".
>Then i create a unique index for fields "field1" and "field2".
>This works, but i want that sql server retrieve me the values thar are bein
g
>duplicated.
>I'm using sql server 7.0.
>Ex: Table1 as the following values:
> -0;0;0;0;
> -1;1;1;1;
> -2;2;2;2;
>I have a second table with the values:
> -3;3;3;3;
> -4;1;1;1;
> -5;0;0;0;
> -6;0;0;0;
>if i do a insert (insert into table1 select * from table2), it gives me an
>error but i want that sql tell me that the error was because of values
>4;1;1;1-5;0;0;0-6;0;0;0.
Hi Joaquim,
A constraint won't do that - a constraint simply constrains, that's all.
As David says, you need to use a query. However, the query he posted
won't return all duplicate rows. Try this one instead:
SELECT T2.key, T2.col1, T2.col2, T2.col3, T2.col4
FROM Table2 AS T2
LEFT JOIN Table1 AS T1
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2
WHERE T1.key IS NOT NULL
OR EXISTS (SELECT *
FROM Table2 AS T2b
WHERE T2b.col1 = T2.col1
AND T2b.col2 = T2.col2
AND T2b.key <> T2.key)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)