Hello,
I am trying to write a query that will execute the same command to each
database in a list. More specifically I use the following command to get a
list of all the user-defined databases on the server:
SELECT catalog_name from information_schema.Schemata
WHERE NOT (CATALOG_NAME in
('tempdb','master','msdb','model','Northwind','pub s'))
and now I want to perfrom that same action (ie dettach or check for orphan
users etc) on each DB that I get from this query. Is there a way to do this
in SQL?
thanks
christos
Christos Kritikos wrote:
> Hello,
> I am trying to write a query that will execute the same command to
> each database in a list. More specifically I use the following
> command to get a list of all the user-defined databases on the server:
> SELECT catalog_name from information_schema.Schemata
> WHERE NOT (CATALOG_NAME in
> ('tempdb','master','msdb','model','Northwind','pub s'))
> and now I want to perfrom that same action (ie dettach or check for
> orphan users etc) on each DB that I get from this query. Is there a
> way to do this in SQL?
> thanks
> christos
Checking user information can be done in sysprocesses. No need to
enumerate the databases. If you really need a way to run the same
command against each database, you can use xp_MSForEachDB or just use a
temp table and interate through the results.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Monday, March 26, 2012
Looping Question!!..........
Hi all,
I have an initial parameter = 'TST0001'
I want to write an INSERT statement to automatically take the initial
parameter 'TST0001' and keep adding 1 to it until it get to 'TST9999'.
Now, my table should store data like these:
TST0001
TST0002
...
...
TST0010
TST0011
...
...
TST9999
Thanks,
Tom dYou can avoid looping by using a Numbers table for this sort of thing. See
the following articles:
http://www.bizdatasolutions.co_m/tsql/tblnumbers.asp
http://www.aspfaq.com/show.asp?id=2516
Here's an example:
CREATE TABLE foo (x VARCHAR(10) PRIMARY KEY)
INSERT INTO foo (x)
SELECT 'TST'+
RIGHT('0000'+CAST(N1.number*100+N2.number AS VARCHAR(4)),4)
FROM master.dbo.spt_values AS N1,
master.dbo.spt_values AS N2
WHERE N1.type = 'P'
AND N1.number BETWEEN 0 AND 99
AND N2.type = 'P'
AND N2.number BETWEEN 0 AND 99
I don't recommend you use this in any persistent code because spt_values
isn't documented. This is just to demonstrate what you can do with auxiliary
tables.
David Portas
SQL Server MVP
--
I have an initial parameter = 'TST0001'
I want to write an INSERT statement to automatically take the initial
parameter 'TST0001' and keep adding 1 to it until it get to 'TST9999'.
Now, my table should store data like these:
TST0001
TST0002
...
...
TST0010
TST0011
...
...
TST9999
Thanks,
Tom dYou can avoid looping by using a Numbers table for this sort of thing. See
the following articles:
http://www.bizdatasolutions.co_m/tsql/tblnumbers.asp
http://www.aspfaq.com/show.asp?id=2516
Here's an example:
CREATE TABLE foo (x VARCHAR(10) PRIMARY KEY)
INSERT INTO foo (x)
SELECT 'TST'+
RIGHT('0000'+CAST(N1.number*100+N2.number AS VARCHAR(4)),4)
FROM master.dbo.spt_values AS N1,
master.dbo.spt_values AS N2
WHERE N1.type = 'P'
AND N1.number BETWEEN 0 AND 99
AND N2.type = 'P'
AND N2.number BETWEEN 0 AND 99
I don't recommend you use this in any persistent code because spt_values
isn't documented. This is just to demonstrate what you can do with auxiliary
tables.
David Portas
SQL Server MVP
--
Friday, March 23, 2012
Looping in Stored Procedures
can we write loops in MSSql stored procedures ?
my aim is to do something like
select * from table
while < recordset is not blank >
do something...
next record
end while
is this possible in stored procedure or should i do this from my client
application ?
pl explaintu can very well do this.
for eg:
create procedure looping
as
begin
declare @.l int
set @.l=10
while @.l>1
begin
print @.l
set @.l=@.l-1
end
end
or it can be a condition like
while exists (select * from table)
begin
some code...
end|||thanks for the information
but have one more qn
im SELECTing some rows from a table
and in the loop, i want to perform some operation on each selcted row
after performing the operations , will the loop moves to the next
record ?
pl comment|||no.
for that u need to use cursors.
but use of cursors is not the recomended way of doing things in sql.
so if u could post what u r trying to do, some one out here will surely be help u out.
my aim is to do something like
select * from table
while < recordset is not blank >
do something...
next record
end while
is this possible in stored procedure or should i do this from my client
application ?
pl explaintu can very well do this.
for eg:
create procedure looping
as
begin
declare @.l int
set @.l=10
while @.l>1
begin
print @.l
set @.l=@.l-1
end
end
or it can be a condition like
while exists (select * from table)
begin
some code...
end|||thanks for the information
but have one more qn
im SELECTing some rows from a table
and in the loop, i want to perform some operation on each selcted row
after performing the operations , will the loop moves to the next
record ?
pl comment|||no.
for that u need to use cursors.
but use of cursors is not the recomended way of doing things in sql.
so if u could post what u r trying to do, some one out here will surely be help u out.
Monday, March 12, 2012
Lookup and OLEDB Command components programming
I need some help on SSIS programming. Can anybody give me a short example or tutorial how to write a code, which will show me use of "Lookup" and "OLEDB Command" components?Here's an example of using a Lookup (amongst other things): http://www.sqlis.com/default.aspx?311
The SCD Wizard produces a data-flow with an OLE DB Command in it so you could look at that!
-Jamie|||And how can I get a code from SCD Wizard?|||
Erch wrote:
And how can I get a code from SCD Wizard?
You can't. The SCD Wizard produces components, not code.
So am I correct in saying that you want to build a package programatically that contains a data-flow containing a LOOKUP and an OLE DB COMMAND? There isn't much resources around yet that shows how to do this - BOL is definately the best place to go.
-Jamie
Friday, March 9, 2012
Look-UP
Can someone help with the follow --
I have a table in SQL and I would like to write a procedure to the following
Date 2001 2003 2006 2012
20060131 0.0455 0.0455 0.0446 0.0422
20060130 0.0566 0.0566 0.0758 0.0436
20060129 0.783 0.5642 0.3548 0.2165
in the pocedure you enter the Date (20060130) and then the Index (2006) the
result should be 0.0758
Please feel free to call me with if you have any questions
847-323-7731
ThanksChrismkr wrote:
> Can someone help with the follow --
> I have a table in SQL and I would like to write a procedure to the
> following
>
> Date 2001 2003 2006 2012
> 20060131 0.0455 0.0455 0.0446 0.0422
> 20060130 0.0566 0.0566 0.0758 0.0436
> 20060129 0.783 0.5642 0.3548 0.2165
> in the pocedure you enter the Date (20060130) and then the Index
> (2006) the result should be 0.0758
>
A better table design would be:
Date Index Value
20060131 2001 .0455
20060131 2003 .0455
20060131 2006 .0446
20060131 2012 .0422
20060130 2001 .0566
20060130 2003 .0566
20060130 2006 .0758
etc.
That makes the procedure easy:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from tablename
where date= @.date and Index= @.index
My recommendation would be to change the database design to what I suggested
above. if you cannot do that for some reason, then you can "fold" your
existing table using a union query in a view, like this:
create view folded_data as
select date, 2001 as Index, 2001 from tablename
union all
select date, 2003 as Index, 2003 from tablename
union all
select date, 2006 as Index, 2006 from tablename
union all
select date, 2012 as Index, 2012 from tablename
Then use the folded_data view in your procedure instead of the base table:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from folded_data
where date= @.date and Index= @.index
However, this may not perform well. You may get better performance via
dynamic sql. See Erland's dynamic sql articles here:
http://www.sommarskog.se/index.html
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob Barrows [MVP] wrote:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
>
Sorry, this should read:
create view folded_data as
select date, 2001 as ValueIndex, [2001] as Value from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Also acceptable would be:
create view folded_data (
Date, ValueIndex, Value
) as
select date, 2001 , [2001] from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Why "ValueIndex"? "Index" is a reserved keyword that should be avoided when
naming database objects.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,
Can I call you or can you call me about this,
We are willing to pay the right price to the right person to help us with
this
Thanks
Chris
"Bob Barrows [MVP]" wrote:
> Chrismkr wrote:
> A better table design would be:
> Date Index Value
> 20060131 2001 .0455
> 20060131 2003 .0455
> 20060131 2006 .0446
> 20060131 2012 .0422
> 20060130 2001 .0566
> 20060130 2003 .0566
> 20060130 2006 .0758
> etc.
> That makes the procedure easy:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from tablename
> where date= @.date and Index= @.index
> My recommendation would be to change the database design to what I suggest
ed
> above. if you cannot do that for some reason, then you can "fold" your
> existing table using a union query in a view, like this:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
> Then use the folded_data view in your procedure instead of the base table:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from folded_data
> where date= @.date and Index= @.index
> However, this may not perform well. You may get better performance via
> dynamic sql. See Erland's dynamic sql articles here:
> http://www.sommarskog.se/index.html
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||To claim your prize, just call them up and give them your credit card
number. ;-)
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:upBlcg7OGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Bob Barrows [MVP] wrote:
> Sorry, this should read:
> create view folded_data as
> select date, 2001 as ValueIndex, [2001] as Value from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Also acceptable would be:
> create view folded_data (
> Date, ValueIndex, Value
> ) as
> select date, 2001 , [2001] from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Why "ValueIndex"? "Index" is a reserved keyword that should be avoided
> when
> naming database objects.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Ah! This explains JT's cryptic message.
Sorry but no. I only work through the newsgroups. I've already got a
full-time job :-)
Bob Barrows
Chrismkr wrote:
> Bob,
> Can I call you or can you call me about this,
> We are willing to pay the right price to the right person to help us
> with this
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
I have a table in SQL and I would like to write a procedure to the following
Date 2001 2003 2006 2012
20060131 0.0455 0.0455 0.0446 0.0422
20060130 0.0566 0.0566 0.0758 0.0436
20060129 0.783 0.5642 0.3548 0.2165
in the pocedure you enter the Date (20060130) and then the Index (2006) the
result should be 0.0758
Please feel free to call me with if you have any questions
847-323-7731
ThanksChrismkr wrote:
> Can someone help with the follow --
> I have a table in SQL and I would like to write a procedure to the
> following
>
> Date 2001 2003 2006 2012
> 20060131 0.0455 0.0455 0.0446 0.0422
> 20060130 0.0566 0.0566 0.0758 0.0436
> 20060129 0.783 0.5642 0.3548 0.2165
> in the pocedure you enter the Date (20060130) and then the Index
> (2006) the result should be 0.0758
>
A better table design would be:
Date Index Value
20060131 2001 .0455
20060131 2003 .0455
20060131 2006 .0446
20060131 2012 .0422
20060130 2001 .0566
20060130 2003 .0566
20060130 2006 .0758
etc.
That makes the procedure easy:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from tablename
where date= @.date and Index= @.index
My recommendation would be to change the database design to what I suggested
above. if you cannot do that for some reason, then you can "fold" your
existing table using a union query in a view, like this:
create view folded_data as
select date, 2001 as Index, 2001 from tablename
union all
select date, 2003 as Index, 2003 from tablename
union all
select date, 2006 as Index, 2006 from tablename
union all
select date, 2012 as Index, 2012 from tablename
Then use the folded_data view in your procedure instead of the base table:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from folded_data
where date= @.date and Index= @.index
However, this may not perform well. You may get better performance via
dynamic sql. See Erland's dynamic sql articles here:
http://www.sommarskog.se/index.html
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob Barrows [MVP] wrote:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
>
Sorry, this should read:
create view folded_data as
select date, 2001 as ValueIndex, [2001] as Value from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Also acceptable would be:
create view folded_data (
Date, ValueIndex, Value
) as
select date, 2001 , [2001] from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Why "ValueIndex"? "Index" is a reserved keyword that should be avoided when
naming database objects.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,
Can I call you or can you call me about this,
We are willing to pay the right price to the right person to help us with
this
Thanks
Chris
"Bob Barrows [MVP]" wrote:
> Chrismkr wrote:
> A better table design would be:
> Date Index Value
> 20060131 2001 .0455
> 20060131 2003 .0455
> 20060131 2006 .0446
> 20060131 2012 .0422
> 20060130 2001 .0566
> 20060130 2003 .0566
> 20060130 2006 .0758
> etc.
> That makes the procedure easy:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from tablename
> where date= @.date and Index= @.index
> My recommendation would be to change the database design to what I suggest
ed
> above. if you cannot do that for some reason, then you can "fold" your
> existing table using a union query in a view, like this:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
> Then use the folded_data view in your procedure instead of the base table:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from folded_data
> where date= @.date and Index= @.index
> However, this may not perform well. You may get better performance via
> dynamic sql. See Erland's dynamic sql articles here:
> http://www.sommarskog.se/index.html
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||To claim your prize, just call them up and give them your credit card
number. ;-)
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:upBlcg7OGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Bob Barrows [MVP] wrote:
> Sorry, this should read:
> create view folded_data as
> select date, 2001 as ValueIndex, [2001] as Value from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Also acceptable would be:
> create view folded_data (
> Date, ValueIndex, Value
> ) as
> select date, 2001 , [2001] from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Why "ValueIndex"? "Index" is a reserved keyword that should be avoided
> when
> naming database objects.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Ah! This explains JT's cryptic message.
Sorry but no. I only work through the newsgroups. I've already got a
full-time job :-)
Bob Barrows
Chrismkr wrote:
> Bob,
> Can I call you or can you call me about this,
> We are willing to pay the right price to the right person to help us
> with this
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
looking to write my own simple SQL Client via TCP/IP
I'm looking to write my own simple SQL Client via TCP/IP. We have some hardware that does not run an OS of sorts, but I can create TCP Connectons. We's like to be able to Query our SQL Server with this hardware.
The SQL Servers we are talking to are MS SQL2000 and MS SQL 2005 both via TCP port 1433. We can telnet to the port of the SQL Server. Any resources to the SQL Protocol you could pass along would be great.
Thank you,
Scott<-
Hi Scott,
In order to create an application driver that can send requests and receive responses from a SQL Server instance, your application must be able to communicate using the Tablular Data Stream (TDS) protocol. The MS TDS protocol is a proprietary protocol and must be licensed from Microsoft. If this interests you, then please let me know and I'll send you information on getting started.
Thanks,
Il-Sung.
Its not too proprietary since there are Linux and Java versions of the TDS Protocol out there with the Source? If its not too much trouble wont hurt to see whats involved in geting the License for the Protocol.
I've found the Following on TDS since I did the original Post:
http://en.wikipedia.org/wiki/Tabular_Data_Stream
http://www.freetds.org/
http://jtds.sourceforge.net/
I'm sure the MS version of it is more full featured when communicating to MS SQL 2005, though I jsut need to return a few Select statements.
I was thinking It might be easier to send a HTTP Request to IIS on the SQL Server and send the Select statement as a parameter and return the Data. I'm looking for something pretty simple as the hardware playform I'm working with is pretty limited.
Thank you,
Scott<-
|||Hi Scott,
Yes, there are free implimentations available although none are endorsed by Microsoft and they are typically based on reverse-engineering efforts. If you'd like to license TDS start with the MS IP Licensing website (http://www.microsoft.com/about/legal/intellectualproperty) and search for TDS.
Alternatively, if your can send HTTP requests from your hardware platform, you can take advantage of the fact that SQL Server 2005 can active as a native web service for SOAP applications. Take a look at the following documentation for more info:
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
http://msdn2.microsoft.com/en-us/library/ms345123.aspx
Il-Sung.
Saturday, February 25, 2012
Looking for sample code for doing store procedures
I'm looking for any good samples that anyone might be aware of on how to write a SQL Server 2005 stored procedure with error handling (using TRY-CATCH blocks) that I can use as a model.
I did find the article "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks" by Rob Garrison. It's pretty good. The article was written based upon an early beta so the author (understandable) wasn't sure about some possible features. I also was disappointed that the examples didn't flow back to showing how the application handle the resulting errors.
If you know of any good article or samples, please let me know.
TIA,
Richard Rosenheim
Please refer to the INSTAWDB.sql script installed with the samples. It has several SP's which use the new TRY...CATCH syntax.|||
Please take a look at the TRY...CATCH topics in Books Online. They also contains lot of examples.
TRY...CATCH (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm
Using TRY...CATCH in Transact-SQL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Monday, February 20, 2012
looking for dups SQL statement
Hi -
I am looking for help to write a SQL statement that looks for duplicates.
I used to know how to do this, so I know it can be done.
It's a SQL statement that uses an embedded SELECT with a GROUP BY and COUNT, then a HAVING clause where the count > 1
In my case, I am starting with this statement
SELECT hhld_id, mail_dt, quote_num, count(*) as count_dups from response
group by hhld_id, mail_dt, quote_num
I am only interested in results having count_dups > 1
How can I achieve these results? It is a very large table (almost 100 million, so it times out when I try and return all results)
Thanks for your help!
ElizabethI'd suggest:SELECT hhld_id, mail_dt, quote_num
, Count(*) as count_dups
FROM response
GROUP BY hhld_id, mail_dt, quote_num
HAVING 1 < Count(*)
ORDER BY hhld_id, mail_dt, quote_num-PatP|||This question that has been asked and answered hundreds if not thousands of times in this and other forums. You should have done some research and "searched" for the answer you needed. :mad:|||This question that has been asked and answered hundreds if not thousands of times in this and other forums. You should have done some research and "searched" for the answer you needed. :mad: You are certainly correct, this question is often asked and usually answered. The problem seems to be that users don't know exactly what to ask until after they know the answer.
It was a civil question, and showed that the user had done a reasonable job of thinking out the problem and expressing their question. I understand your frustration at seeing the same question repeatedly (I'm not overly enthused about it myself), but until we can find a way that users can relatively reliably find the answer for themselves, I will probably just go on answering it! ;)
-PatP|||I didn't intend to be so harsh, but I beleive there also may be a way to instruct people to do some research before posting questions that have been answered many times in the past. :shocked:|||Oh I'm with you on this one, I just can't find the "magic bullet" to get the job done.
I'm not at all sure that I've even given the user anything that they didn't have before. They had all of the pieces, and by offering my first guess at a solution all I really did was start a conversation rolling... If they respond and say "thanks" I'll be surprised. If they point out what's bothering them about my idea, at least I'll know a bit more toward helping them find a solution.
My gut feel is that this poster is pretty savvy. I don't think we've even scratched the surface of the real problem underlying this post, but at least now we've got the potential for a conversation, which is more than we had!
-PatP|||Maybe we could point them to this link (http://www.dbforums.com/showthread.php?t=1031644) (or similar). :beer:
I am looking for help to write a SQL statement that looks for duplicates.
I used to know how to do this, so I know it can be done.
It's a SQL statement that uses an embedded SELECT with a GROUP BY and COUNT, then a HAVING clause where the count > 1
In my case, I am starting with this statement
SELECT hhld_id, mail_dt, quote_num, count(*) as count_dups from response
group by hhld_id, mail_dt, quote_num
I am only interested in results having count_dups > 1
How can I achieve these results? It is a very large table (almost 100 million, so it times out when I try and return all results)
Thanks for your help!
ElizabethI'd suggest:SELECT hhld_id, mail_dt, quote_num
, Count(*) as count_dups
FROM response
GROUP BY hhld_id, mail_dt, quote_num
HAVING 1 < Count(*)
ORDER BY hhld_id, mail_dt, quote_num-PatP|||This question that has been asked and answered hundreds if not thousands of times in this and other forums. You should have done some research and "searched" for the answer you needed. :mad:|||This question that has been asked and answered hundreds if not thousands of times in this and other forums. You should have done some research and "searched" for the answer you needed. :mad: You are certainly correct, this question is often asked and usually answered. The problem seems to be that users don't know exactly what to ask until after they know the answer.
It was a civil question, and showed that the user had done a reasonable job of thinking out the problem and expressing their question. I understand your frustration at seeing the same question repeatedly (I'm not overly enthused about it myself), but until we can find a way that users can relatively reliably find the answer for themselves, I will probably just go on answering it! ;)
-PatP|||I didn't intend to be so harsh, but I beleive there also may be a way to instruct people to do some research before posting questions that have been answered many times in the past. :shocked:|||Oh I'm with you on this one, I just can't find the "magic bullet" to get the job done.
I'm not at all sure that I've even given the user anything that they didn't have before. They had all of the pieces, and by offering my first guess at a solution all I really did was start a conversation rolling... If they respond and say "thanks" I'll be surprised. If they point out what's bothering them about my idea, at least I'll know a bit more toward helping them find a solution.
My gut feel is that this poster is pretty savvy. I don't think we've even scratched the surface of the real problem underlying this post, but at least now we've got the potential for a conversation, which is more than we had!
-PatP|||Maybe we could point them to this link (http://www.dbforums.com/showthread.php?t=1031644) (or similar). :beer:
Subscribe to:
Comments (Atom)