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,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or AggregationTitle4 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,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or
AggregationTitle4 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,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or AggregationTitle4 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,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or
AggregationTitle4 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, aggregationtitle1 VARCHAR(10) NULL,
aggregationtitle2 VARCHAR(10) NULL, aggregationtitle3 VARCHAR(10) NULL,
aggregationtitle4 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, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitle1 IS NOT NULL
OR aggregationtitle2 IS NOT NULL
OR aggregationtitle3 IS NOT NULL
OR aggregationtitle4 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_title1, aggregation_title2,
aggregation_title3, aggregation_title4) 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_title 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_title1, aggregation_title2,
aggregation_title3, aggregation_title4) 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_detail
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_detail
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*******@earthlink.net> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com... Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_title1, aggregation_title2,
aggregation_title3, aggregation_title4) 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_title 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.