renaming a table

how do i rename a table? i tried making a copy of it (select * into copy from original), but it turns out that doesn't work either... =(The method for renaming objects depends on which database engine you are using.

-PatP|||In SQL 2000
Select * into newTable from OldTable
drop Table OldTable

Madhivanan|||in sql serversp_rename 'tablename' , 'newname'

Removing HASH Match / Inner Join

Hi,

I'm at my wits end! I have two large tables one with 1.2mill one with 2.3 mill and they are very wide tables. I have a select with an inner join. All columns used in the join are contained in indexes. But it does an index scan and a massive hashmatch. Why is this? both tables have columns in the index ordered the same, all datatypes of the indexes are ints. The code looks like this.

SELECT TblActivities.*
FROM TblActivities
INNER JOIN TblBookingsCraig
ON
TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion

Does anyone know why it won't perform an index seek?

Cheers
CYou should have the indexes on the fields you want to join, do you have them like that?

TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion|||Hi,

Thanks for the reply. Yes I do have the indexes identically setup on both tables. Could it be ignoring the indexes because of the size difference of the two tables? One has 1millish and the other 2.5mill ish?

Cheers
C|||Did you check the values of these columns? if they contain duplicate values or highly identical values for number of records; say for 100 records at least the indexes have same values. In this case, the index scan would be the decision that SQL optimizer might take!

Removing Duplicate Rows Issue

I'm trying to remove duplicate rows from a table. tableA has 2 cols. acctid
and col1 When I run:
select count(distinct accid) from tableA
I get 10 as a result(I guess there are 10 distinct rows in the table,
correct?), but when I run:
select distinct acctid, col2 into #temp from tableA
(those are the only 2 columns in the table) I get 11 as the result, How
come? Shouldn't it say 10 rows affexted and not 11? any ideas?
thanksCan you show us the structure of tableA, and the INSERT statements required
to populate it with these 10 or 11 rows?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> I'm trying to remove duplicate rows from a table. tableA has 2 cols.
acctid
> and col1 When I run:
> select count(distinct accid) from tableA
> I get 10 as a result(I guess there are 10 distinct rows in the table,
> correct?), but when I run:
> select distinct acctid, col2 into #temp from tableA
> (those are the only 2 columns in the table) I get 11 as the result, How
> come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> thanks|||tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
when I do select count(distinct acctid) from tableA I get 10 rows as the
result, then when I select distinct aactid, col1 into #temp from tablea I get
11 rows, Why is this the case? Thank you
"Aaron [SQL Server MVP]" wrote:
> Can you show us the structure of tableA, and the INSERT statements required
> to populate it with these 10 or 11 rows?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> acctid
> > and col1 When I run:
> > select count(distinct accid) from tableA
> > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > correct?), but when I run:
> > select distinct acctid, col2 into #temp from tableA
> > (those are the only 2 columns in the table) I get 11 as the result, How
> > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > thanks
>
>|||Because you added a column to your distinct clause in the second query.
select distinct aactid, col1 from tablea
vs
select distinct aactid from tablea
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> when I do select count(distinct acctid) from tableA I get 10 rows as the
> result, then when I select distinct aactid, col1 into #temp from tablea I
get
> 11 rows, Why is this the case? Thank you
> "Aaron [SQL Server MVP]" wrote:
> > Can you show us the structure of tableA, and the INSERT statements
required
> > to populate it with these 10 or 11 rows?
> > http://www.aspfaq.com/5006
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > acctid
> > > and col1 When I run:
> > > select count(distinct accid) from tableA
> > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > correct?), but when I run:
> > > select distinct acctid, col2 into #temp from tableA
> > > (those are the only 2 columns in the table) I get 11 as the result,
How
> > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > thanks
> >
> >
> >|||that was an example, what I'm really trying to do is remove duplicate by
these statements
select distinct acctid, col1 into #temp from tableA
truncate table tableA
insert into tableA select * from #temp
the logic is in the previous posts!
"Jeff Dillon" wrote:
> Because you added a column to your distinct clause in the second query.
> select distinct aactid, col1 from tablea
> vs
> select distinct aactid from tablea
> "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > tableA has 2 columns acctid and col1. lets say there are 20 rows of data,
> > when I do select count(distinct acctid) from tableA I get 10 rows as the
> > result, then when I select distinct aactid, col1 into #temp from tablea I
> get
> > 11 rows, Why is this the case? Thank you
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > Can you show us the structure of tableA, and the INSERT statements
> required
> > > to populate it with these 10 or 11 rows?
> > > http://www.aspfaq.com/5006
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > I'm trying to remove duplicate rows from a table. tableA has 2 cols.
> > > acctid
> > > > and col1 When I run:
> > > > select count(distinct accid) from tableA
> > > > I get 10 as a result(I guess there are 10 distinct rows in the table,
> > > > correct?), but when I run:
> > > > select distinct acctid, col2 into #temp from tableA
> > > > (those are the only 2 columns in the table) I get 11 as the result,
> How
> > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > thanks
> > >
> > >
> > >
>
>|||Yes, your logic is wrong, per my response.
You are aware that distinct acctid, col1 means take (acctid, col1) together,
and find all distinct rows. Distinct doesn't only apply to the first column
before your comma
To find duplicates:
select count(id), id from a
group by id
having count(id) > 1
Jeff
"mikeb" <mikeb@.discussions.microsoft.com> wrote in message
news:2B455174-0A8A-47E3-BB71-AA26CBE37D0E@.microsoft.com...
> that was an example, what I'm really trying to do is remove duplicate by
> these statements
> select distinct acctid, col1 into #temp from tableA
> truncate table tableA
> insert into tableA select * from #temp
> the logic is in the previous posts!
> "Jeff Dillon" wrote:
> > Because you added a column to your distinct clause in the second query.
> >
> > select distinct aactid, col1 from tablea
> >
> > vs
> >
> > select distinct aactid from tablea
> >
> > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > news:B00C82BB-9575-4353-96F5-8D70F6736560@.microsoft.com...
> > > tableA has 2 columns acctid and col1. lets say there are 20 rows of
data,
> > > when I do select count(distinct acctid) from tableA I get 10 rows as
the
> > > result, then when I select distinct aactid, col1 into #temp from
tablea I
> > get
> > > 11 rows, Why is this the case? Thank you
> > >
> > > "Aaron [SQL Server MVP]" wrote:
> > >
> > > > Can you show us the structure of tableA, and the INSERT statements
> > required
> > > > to populate it with these 10 or 11 rows?
> > > > http://www.aspfaq.com/5006
> > > >
> > > > --
> > > > http://www.aspfaq.com/
> > > > (Reverse address to reply.)
> > > >
> > > >
> > > >
> > > >
> > > > "mikeb" <mikeb@.discussions.microsoft.com> wrote in message
> > > > news:0FD10B9F-7B8E-4870-AD2E-8F670F36FA20@.microsoft.com...
> > > > > I'm trying to remove duplicate rows from a table. tableA has 2
cols.
> > > > acctid
> > > > > and col1 When I run:
> > > > > select count(distinct accid) from tableA
> > > > > I get 10 as a result(I guess there are 10 distinct rows in the
table,
> > > > > correct?), but when I run:
> > > > > select distinct acctid, col2 into #temp from tableA
> > > > > (those are the only 2 columns in the table) I get 11 as the
result,
> > How
> > > > > come? Shouldn't it say 10 rows affexted and not 11? any ideas?
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
> >

removing duplicate rows

Hi,
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggreate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
Smithanot very efficient - and prone to possible truncation of col2 -

select distinct col1, cast(col2 as varchar(8000)) from test|||Thanks. I need the output to be with the same datatype, since I need to create temp tables using the selected data(using SELECT INTO)|||again not very efficient:

select temp.col1, cast(temp.newcol2 as text) col2
into newtable
from
(select distinct col1, cast(col2 as varchar(8000)) newcol2 from test) temp

removing duplicate rows

Hi,

Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.

Table :

create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go

Please advise,

Thanks,
MiraJTry converting the columns to, for instance, varchar by using CAST() and then concatenating them to one string. Before going on with DISTINCT or whatever needed.|||I want the output to be in the same datatype. Hence casting will not solve my purpose.|||nabucco's suggestion works:

select cast(col2 as text) col2
from (
select distinct cast(col2 AS varchar(100)) col2 from test
) x

col2
--
abc
dbc|||nabucco's suggestion works:

select cast(col2 as text) col2
from (
select distinct cast(col2 AS varchar(100)) col2 from test
) x

col2
--
abc
dbc

How will u solve the issue if the text data is bigger than 8000 ?|||Just a thought ...

Can use substring and then concatenate every 8000 characters. Will it work !|||I'm afraid not. You can not concatenate text datatype

create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
select col2 + col2 from test

(1 row(s) affected)

Server: Msg 403, Level 16, State 1, Line 2
Invalid operator for data type. Operator equals add, type equals text.|||Go thru this sample script,it may help u.

Use Northwind
go
select * into t from Employees
go
--inserting duplicate records----
insert into t
(
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
)
select
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
from t where EmployeeID in (2,1,4,5,8)

go
--------delete duplicate------
-- Use this query to findout the maximum lenth in text field.here maximum is 21000 bytes, so I used substring function up to that level(eg:substring(Photo,16001,8000)) in query.

--SELECT max(datalength(Photo)) FROM Employees
while (0=0)
begin
delete t from t t1 inner join

(
select max(E1ID) as E1ID,max(E2ID) as E2ID,sumvalue from
(
SELECT
E1.EmployeeID as E1ID
,E2.EmployeeID as E2ID, (E1.EmployeeID +E2.EmployeeID) as sumvalue
FROM
t E1 INNER JOIN t E2 ON
substring(E1.Photo, 1, 8000) = substring(E2.Photo, 1, 8000)
AND substring(E1.Photo, 8001, 8000) = substring(E2.Photo, 8001, 8000)
AND substring(E1.Photo, 16001, 8000) = substring(E2.Photo, 16001, 8000)
) as tm
group by sumvalue
having count(*)>=2
) tm1
on tm1.E1ID=t1.EmployeeID
if @.@.rowcount=0 break
end
go
--Unique records----
select * from t

Removing dupes (distinct like)

Greets, prob. simple problem for experts but my sql is at it's infancy :)

select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID

Gives me:
5266
5266
5266
5088
5088

etc, but I want only unique entity oids returned:
5266
5088

How I can achieve this w/ SQL as distinct no workie bcause of order by?

Thanks
--POTry a GROUP BY instead of ORDER BY. It should still force the sort.|||I'd use both GROUP BY and ORDER BY to get what you've described.

-PatP|||select Entity.OID from Entity
,EN,ENXP as ENXP0

where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
group by ENXP0.ST_NAMEROW,Entity.OID
order by ENXP0.ST_NAMEROW,Entity.OID

(and other combinations) seems still produce exact same dupe OID's.

group by Entity.OID
order by ENXP0.ST_NAMEROW,Entity.OID

won't work (on SQL Server 2000 atleast as ENXP0.ST_NAMEROW must be included also on group by)

Any other suggestions?

--PO|||Did you try a subselect?

select distinct Entity.OID from
(select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID)|||Yepp, unfortunately SQL server does not allow order by's at subselects (otherwise prob could be solved easily ;)

Server: Msg 1033, Level 15, State 1, Line 10
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

:(

TOP stuff is not really applicable cause I must fetch all rows possible and seems that SQL server does not understand it inside inline select.

Damn, this *looks* like piece of cake to solve...|||what does "distinct no workie bcause of order by" mean?

of course you can have DISTINCT and ORDER BY

however, since you are returning only one column, it seems rather unnecessary

rule of thumb: select what you want to order

if you want to order your results by order by ENXP0.ST_NAMEROW and Entity.OID, then you should select both of these columns

so, what was your question again?|||what does "distinct no workie bcause of order by" mean?

of course you can have DISTINCT and ORDER BY

however, since you are returning only one column, it seems rather unnecessary

rule of thumb: select what you want to order

if you want to order your results by order by ENXP0.ST_NAMEROW and Entity.OID, then you should select both of these columns

so, what was your question again?

"Distinct no workie" means that for example this won't ofcourse work:

select distinct Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Anyway we need those OID's and *only unique* OID's ordered by ENXP0.ST_NAMEROW. OID's are later used on another Query to fetch other rows from db (on that particular order made by ENXP0.ST_NAMEROW). Reason is that our system is using SQL generator to generate SQL clauses and reason for that is that writing SQL by hand is far too slow and difficult todo against schema we are using.

Thanks,|||i think you're missing the point

here are a bunch of OIDs --

5266
5088
5937
5013

how can you tell what their ENXP0.ST_NAMEROW values are? you can't

therefore how would you know if they were, or were not, in ENXP0.ST_NAMEROW order? you can't

you simply cannot order OID values by ENXP0.ST_NAMEROW|||For example:

select ENXP.ST_NAMEROW from ENXP
where ENXP.TOID in (5266)

(as we have duplicate main oid on ENXP table)

and if we can get actual OID's ordered by ENXP.ST_NAMEROW using query like:

select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID

which orders them by ENXP0.ST_NAMEROW so do we have OID's ordered by NAMEROW? I think so, but with alot of duplicate values because name is formed using multiple ENXP0.ST_NAMEROW's.|||well, good luck to you sir, i do not understand what you're doing and i cannot help you further|||well, good luck to you sir, i do not understand what you're doing and i cannot help you further

Yep, thanks.
Well basic question was that is there way using SQL to remove duplicate OID's from resultset what this query return (and it must return only OID's ordered by ENXP0.ST_NAMEROW) :

select Entity.OID from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW

Result:
5266
5266
5266

Can be done ofcourse on server side as last resort if cannot be done using SQL.

Query like:
select distinct Entity.OID,ENXP0.ST_NAMEROW from Entity
,EN,ENXP as ENXP0
where Entity.OID=EN.TOID
and EN.CS_ST_USE='L'
and EN.OID=ENXP0.POID
and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
and ENXP0.CS_ST_TYPE='GIV'
and ENXP0.CS_ST_QUALIFIER=null
order by ENXP0.ST_NAMEROW,Entity.OID

Will return for example:
5266 P
5266 Pamela
5266 Prognost

But again we need only those unique OID's returned, no other data at this point.