REMOVE DUPLICATE ROWS

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

DiegoHi

There are several ways of doing this... You can select distinct rows into a
temporary table (See DISTINCT in books online), clear out your main table
and then re-populate it. If you have an differentiating column then you can
use that to delete rows that are not (say) the minimum value of that
column, or you could stop rows being put in the table in the first place by
having a unique index or primary key over the columns that should be
distinct, or using a not exists clause when inserting the data.

E.g.
..
SELECT DISTINCT *
INTO #SimpleExample
FROM Test

TRUNCATE TABLE TEST

INSERT INTO TEST ( c1, c2, c3 )
SELECT * FROM #SimpleExample

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc which would have
been useful. See http://www.aspfaq.com/etiquette.asp?id=5006

John

"Diego Rey" <diegobph@.yahoo.com> wrote in message
news:e09be785.0412042052.52c8c7e5@.posting.google.c om...
> Hi everyone.
> How can I get the unique row from a table which contains multiple rows
> that have exactly the same values.
> example:
> create table test (
> c1 as smallint,
> c2 as smallint,
> c3 as smallint )
> insert into test values (1,2,3)
> insert into test values (1,2,3)
> i want to remove whichever of the rows but I want to retain a single
> row.
> TIA
> Diego|||John Bell (jbellnewsposts@.hotmail.com) writes:
> As there is no AS in a CREATE TABLE statement you will have problems with
> this DDL, there is also no information regarding PKs etc

Obviously, if he has identical rows in his table, there is no primary key.

Which all good tables in relational database is supposed to have, and thus
this explains why this operation is not a trivial one to perform. You are
simply not supposed to wind up in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Obviously, if he has identical rows in his table, there is no primary
>> key...Which all good tables in relational database is supposed to have,

Is that a "relational heresy" or a "relational orthodoxy" or an "attempt to
appease the ideas of relational theory" ?( ..kidding :-) )

--
Anithsql

remove all double quotes from column values using t-sql

Hi,
I am having problem withj double quotes being inserted automatically when i
am inserting data using a CSV file.I am using a C# program to insert the
values.
My coulmn is called whereClause and it is a varchar(50) e.g
'ISNULL(salary,2000)=2000'
but what gets inserted is "'ISNULL(salary,2000)=2000'"
I am using this value in dynamic sql to generate a query such as
exec 'select salary from employee where '+@.whereClause and I get error
because of the automatic double quote insertion.
The CSV file does not have double quotes so I need a solution that will do
either 1 of the following
1)suppress insertion of double quotes in the table in C# OR
2)write query to remove the double quotes using T-SQL
Solution 2 is preferable.
Your help is highly appreciated.
Thanks.if I understood the question correctly, this should work..
declare @.a varchar(30)
set @.a = '"ISNULL(salary,2000)=2000"'
select replace(@.a,'"','')|||Thans for the reply.
But the problem is I have about a thousand rows and each condition is
different and having double quotes eg
id whereClause
1 "ISNULL(salary,5000)=5000"
2 "ISNULL(bonus,400)>600"
3 "IN(600,40)"
I want to remove all double quotes and update the table with proper values i
e
if table contains "ISNULL(salary,5000)=5000" I want to update it as
ISNULL(salary,5000)=5000
Thanks.
"Omnibuzz" wrote:

> if I understood the question correctly, this should work..
> declare @.a varchar(30)
> set @.a = '"ISNULL(salary,2000)=2000"'
> select replace(@.a,'"','')|||Use REPLACE:
create table #x (s varchar(30))
INSERT #x values ('abcdefg')
INSERT #x values ('"hijklmn"')
INSERT #x values ('o"p"q"r"s"t"u')
select * from #x
UPDATE #x
SET S = REPLACE(S,'"','')
select * from #x
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 11:18:02 -0700, tech77
<tech77@.discussions.microsoft.com> wrote:

>Hi,
>I am having problem withj double quotes being inserted automatically when i
>am inserting data using a CSV file.I am using a C# program to insert the
>values.
>My coulmn is called whereClause and it is a varchar(50) e.g
>'ISNULL(salary,2000)=2000'
>but what gets inserted is "'ISNULL(salary,2000)=2000'"
>I am using this value in dynamic sql to generate a query such as
>exec 'select salary from employee where '+@.whereClause and I get error
>because of the automatic double quote insertion.
>The CSV file does not have double quotes so I need a solution that will do
>either 1 of the following
>1)suppress insertion of double quotes in the table in C# OR
>2)write query to remove the double quotes using T-SQL
>Solution 2 is preferable.
>Your help is highly appreciated.
>Thanks.|||Thanks.That worked like a charm!!
"Roy Harvey" wrote:

> Use REPLACE:
> create table #x (s varchar(30))
> INSERT #x values ('abcdefg')
> INSERT #x values ('"hijklmn"')
> INSERT #x values ('o"p"q"r"s"t"u')
> select * from #x
> UPDATE #x
> SET S = REPLACE(S,'"','')
> select * from #x
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 11:18:02 -0700, tech77
> <tech77@.discussions.microsoft.com> wrote:
>
>sql

Remove <select a value> option

I have a parameter that get his values from a query.
his defualt value is Null.
In the report page, The first value is "<select a value>".
even if I give the parameter another defualt value Then it open with
this value selected but still the first value is "<select a value>".
Can I remove this?
Thanks.On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> I have a parameter that get his values from a query.
> his defualt value is Null.
> In the report page, The first value is "<select a value>".
> even if I give the parameter another defualt value Then it open with
> this value selected but still the first value is "<select a value>".
> Can I remove this?
> Thanks.
Please refer to the response I posted on the other Reporting Services
group.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Where is the other reporting services group..I can't find it.
Cheers
D
"EMartinez" wrote:
> On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> > I have a parameter that get his values from a query.
> > his defualt value is Null.
> >
> > In the report page, The first value is "<select a value>".
> > even if I give the parameter another defualt value Then it open with
> > this value selected but still the first value is "<select a value>".
> >
> > Can I remove this?
> >
> > Thanks.
> Please refer to the response I posted on the other Reporting Services
> group.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc o
n
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same
,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
--
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:
> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> > I've got an interesting situation. I have two SQL 2000 servers and a proc
> > on
> > one that updates data on the other. If I use literal values in the where
> > clause of the update statement then the query plan uses a "remote query"
> > to
> > do the update. If I use variables then it uses a "remote scan" and
> > filters
> > locally. Obviously that takes too long.
> >
> > Here is some sample script.
> > -- This uses a remote query
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = 'ROB'
> > WHERE OrderNumber = 12345
> >
> > -- This uses a remote scan
> > DECLARE @.Salesperson CHAR(3)
> > DECLARE @.OrderNumber INT
> > SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> >
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > -- This uses a remote query; same table and index structure; linked server
> > defined the same
> > UPDATE [Server3].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > I can't figure out any reason why it does it that way. To make things
> > more
> > odd, that proc updates a couple other servers in the same way for the same
> > table and those use remote queries. The linked servers are defined the
> > same,
> > the table structure and indexes are the same.
> >
> > Does anyone have any idea why it would work this way?
> >
> > Thanks.
> >
> > Dale.
> >
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.
Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>
|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>