No distinct in a select into stement ?

[Daniel Wetzler]

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or AggregationTitl e4 is not NULL;
This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or
AggregationTitl e4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler
I

[David Portas]

Daniel Wetzler wrote:

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or AggregationTitl e4 is not NULL;
This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or
AggregationTitl e4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler

The IDENTITY function makes each row unique so DISTINCT doesn't
eliminate the duplicates in this case. Interestingly, this behaviour
seems to have changed in SQL Server 2005. If I run your SELECT INTO on
2005 I get a different execution plan with the IDENTITY value computed
after DISTINCT.

For 2000 the workaround is easy. The following should insert just one
row into vartitles.

CREATE TABLE variables (title1 VARCHAR(10) NULL, title2 VARCHAR(10)
NULL, title3 VARCHAR(10) NULL, aggregationtitl e1 VARCHAR(10) NULL,
aggregationtitl e2 VARCHAR(10) NULL, aggregationtitl e3 VARCHAR(10) NULL,
aggregationtitl e4 VARCHAR(10) NULL);

INSERT INTO variables VALUES ('1','1','1','1 ','1','1','1');
INSERT INTO variables VALUES ('1','1','1','1 ','1','1','1');

SELECT IDENTITY (INT) AS id,
title1, title2, title3, aggregationtitl e1, aggregationtitl e2,
aggregationtitl e3, aggregationtitl e4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitl e1, aggregationtitl e2,
aggregationtitl e3, aggregationtitl e4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitl e1 IS NOT NULL
OR aggregationtitl e2 IS NOT NULL
OR aggregationtitl e3 IS NOT NULL
OR aggregationtitl e4 IS NOT NULL) AS V ;

SELECT * FROM vartitles ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Daniel Wetzler]

Thank you very much.
This was a very helpful hint.

Best regards,

Daniel

[--CELKO--]

Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_tit le1, aggregation_tit le2,
aggregation_tit le3, aggregation_tit le4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_tit le instead of this convoluted query.

[Tony Rogerson]

> Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_tit le1, aggregation_tit le2,
aggregation_tit le3, aggregation_tit le4) IS NOT NULL
and.... bang goes performance too other than a probable clustered index scan
/ table scan.

Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums....

There is a non-clustered index on nntp_author and there is non-clustered
index on author_id

-- Query 1
select count(*)
from mb_message_deta il
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_deta il
where coalesce( nntp_author, author_id ) is not null

Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan

Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%

Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk......

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** *************@t 31g2000cwb.goog legroups.com... Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_tit le1, aggregation_tit le2,
aggregation_tit le3, aggregation_tit le4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_tit le instead of this convoluted query.

[J.Evans.1970]

--------------------
Seriously, go and get a junior job as a programmer and get some very
needed
industrial / real world experience instead of bashing people down on
here,
as far as 'newbie' goes - you have room to talk......
--------------------

<BIG GRIN> from on who has been the receiving side of one of CELKO's
many put-downs.