remove duplicate rows

i have a table with NO constraints and duplicate rows(e.g one row is inserted four times) now I want to remove the rows in such a way that only one row from the duplicate rows should stay in the table. Is there any query to solve this problem.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Here is your solution:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q139444
"enoch jadhav" <enochjadhav@.myway.com> wrote in message
news:ejrKmIblDHA.424@.TK2MSFTNGP10.phx.gbl...
> i have a table with NO constraints and duplicate rows(e.g one row is
inserted four times) now I want to remove the rows in such a way that only
one row from the duplicate rows should stay in the table. Is there any query
to solve this problem.
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

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 10 000 records limit

Hi,
Our users that connect to a MSSQL db via MS-Access cannot access more than
10 000 records. In other words, all the records that have been inserted afte
r
the 10 000th record cannot be access nor from the table, nor from a query.
Is there any way to overcome the problem?
ThanksHere's a not so awesome way:
Create all your tables without data. When they need data you create a
connection in code and snapshot the data out of SQL into your Access
tables.
Frankly, that just sux. I've not heard of a 10,000 record limit in Access.
Can you point me to an MSDN are similar article that talks about this. I
would be interested in looking into this a little more.

> Our users that connect to a MSSQL db via MS-Access cannot access more than
> 10 000 records. In other words, all the records that have been inserted
> after the 10 000th record cannot be access nor from the table, nor from a
> query.|||We have users using linked tables in Access to connect to SQL Server and
there is no such limit.
Ben Nevarez
"DexterV" <DexterV@.discussions.microsoft.com> wrote in message
news:CA155716-7F98-4D64-9318-EF816ADA42BB@.microsoft.com...
> Hi,
> Our users that connect to a MSSQL db via MS-Access cannot access more than
> 10 000 records. In other words, all the records that have been inserted
> after
> the 10 000th record cannot be access nor from the table, nor from a query.
> Is there any way to overcome the problem?
> Thanks|||Dexter,
This is an option in the ms-access settings, probably to avoid long lists
being sent accross the network,
to augment the limit: I'll try to guide you (my ms-access version is in dutc
h)
go to the ms-access menu tools / options, the option dialog box opens.
There is a tab called edit/find and in there is there is text box with the
limit number like 10000
Hope this helps
--
Jan D''Hondt
<Toto, I''ve got a feeling we''re not in Kansas anymore.>
SQL database and .NET solutions
"DexterV" wrote:

> Hi,
> Our users that connect to a MSSQL db via MS-Access cannot access more than
> 10 000 records. In other words, all the records that have been inserted af
ter
> the 10 000th record cannot be access nor from the table, nor from a query.
> Is there any way to overcome the problem?
> Thanks|||Thanks Jan,
Actually, I found the max. record limit parameter on the advanced tab.
I should have explored Ms-access options first.
"Jan D''Hondt" wrote:
[vbcol=seagreen]
> Dexter,
> This is an option in the ms-access settings, probably to avoid long lists
> being sent accross the network,
> to augment the limit: I'll try to guide you (my ms-access version is in du
tch)
> go to the ms-access menu tools / options, the option dialog box opens.
> There is a tab called edit/find and in there is there is text box with the
> limit number like 10000
> Hope this helps
> --
> Jan D''Hondt
> <Toto, I''ve got a feeling we''re not in Kansas anymore.>
> SQL database and .NET solutions
>
> "DexterV" wrote:
>

remove 10 000 records limit

Hi,
Our users that connect to a MSSQL db via MS-Access cannot access more than
10 000 records. In other words, all the records that have been inserted after
the 10 000th record cannot be access nor from the table, nor from a query.
Is there any way to overcome the problem?
Thanks
Here's a not so awesome way:
Create all your tables without data. When they need data you create a
connection in code and snapshot the data out of SQL into your Access
tables.
Frankly, that just sux. I've not heard of a 10,000 record limit in Access.
Can you point me to an MSDN are similar article that talks about this. I
would be interested in looking into this a little more.

> Our users that connect to a MSSQL db via MS-Access cannot access more than
> 10 000 records. In other words, all the records that have been inserted
> after the 10 000th record cannot be access nor from the table, nor from a
> query.
|||We have users using linked tables in Access to connect to SQL Server and
there is no such limit.
Ben Nevarez
"DexterV" <DexterV@.discussions.microsoft.com> wrote in message
news:CA155716-7F98-4D64-9318-EF816ADA42BB@.microsoft.com...
> Hi,
> Our users that connect to a MSSQL db via MS-Access cannot access more than
> 10 000 records. In other words, all the records that have been inserted
> after
> the 10 000th record cannot be access nor from the table, nor from a query.
> Is there any way to overcome the problem?
> Thanks
|||Dexter,
This is an option in the ms-access settings, probably to avoid long lists
being sent accross the network,
to augment the limit: I'll try to guide you (my ms-access version is in dutch)
go to the ms-access menu tools / options, the option dialog box opens.
There is a tab called edit/find and in there is there is text box with the
limit number like 10000
Hope this helps
Jan D''Hondt
<Toto, I''ve got a feeling we''re not in Kansas anymore.>
SQL database and .NET solutions
"DexterV" wrote:

> Hi,
> Our users that connect to a MSSQL db via MS-Access cannot access more than
> 10 000 records. In other words, all the records that have been inserted after
> the 10 000th record cannot be access nor from the table, nor from a query.
> Is there any way to overcome the problem?
> Thanks
|||Thanks Jan,
Actually, I found the max. record limit parameter on the advanced tab.
I should have explored Ms-access options first.
"Jan D''Hondt" wrote:
[vbcol=seagreen]
> Dexter,
> This is an option in the ms-access settings, probably to avoid long lists
> being sent accross the network,
> to augment the limit: I'll try to guide you (my ms-access version is in dutch)
> go to the ms-access menu tools / options, the option dialog box opens.
> There is a tab called edit/find and in there is there is text box with the
> limit number like 10000
> Hope this helps
> --
> Jan D''Hondt
> <Toto, I''ve got a feeling we''re not in Kansas anymore.>
> SQL database and .NET solutions
>
> "DexterV" wrote: