|
|
Posted by David Portas on 04/24/06 20:22
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|