Hi, is there a way I can check if a table is empty and contains no
rows?
I have a Table1 which being dynamic can sometimes end up with now
columns what so ever. I'm using Table1 in one of my views along with 2
other tables and I would like put a condition at the...something like
AND Table1 IS NOTEMPTY
Is there a way to do this in MS SQL?
Many thanks
Yas
On 18 Aug, 15:52, Yas
> Hi, is there a way I can check if a table is empty and contains no
> rows?
>
> I have a Table1 which being dynamic can sometimes end up with now
> columns what so ever. I'm using Table1 in one of my views along with 2
> other tables and I would like put a condition at the...something like
> AND Table1 IS NOTEMPTY
>
Actually could I possibly do something like the following? OR is there
a better way?
DECLARE @myCount int
SELECT @myCount = (select count(*) FROM Table1)
SELECT xyz
FROM Table1, Table2 etc joins etc etc
WHERE .... AND @mycount > 10
Cheers
Yas
On 18 Aug, 16:22, Yas
> On 18 Aug, 15:52, Yas
>
> > Hi, is there a way I can check if a table is empty and contains no
> > rows?
>
> > I have a Table1 which being dynamic can sometimes end up with now
> > columns what so ever. I'm using Table1 in one of my views along with 2
> > other tables and I would like put a condition at the...something like
> > AND Table1 IS NOTEMPTY
>
> Actually could I possibly do something like the following? OR is there
> a better way?
>
> DECLARE @myCount int
> SELECT @myCount = (select count(*) FROM Table1)
>
> SELECT xyz
> FROM Table1, Table2 etc joins etc etc
>
> WHERE .... AND @mycount > 10
>
Ahhhh but the problem is I cant use DECLARE in a VIEW :-(
is there an alternative?
Thanks
Yas (yasar1@gmail.com) writes:
> Hi, is there a way I can check if a table is empty and contains no
> rows?
>
> I have a Table1 which being dynamic can sometimes end up with now
> columns what so ever. I'm using Table1 in one of my views along with 2
> other tables and I would like put a condition at the...something like
> AND Table1 IS NOTEMPTY
>
AND EXISTS (SELECT * FROM Table1)
The EXISTS / NOT EXISTS are part of the essential SQL anyone who is
working with SQL should learn. The above example is simple, but say
you want all customers who have placed an order / who have never placed
an order:
SELECT C.CustomerID, C.CompanyName
FROM Customers C
WHERE /* NOT */ EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
as you get problem if you have a multi-column key. Plus that NOT IN
can trap you if NULL values are involved.
The query above runs in the Northwind database, if you want to try it.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On 18 Aug, 22:14, Erland Sommarskog
> Yas (yas...@gmail.com) writes:
> > Hi, is there a way I can check if a table is empty and contains no
> > rows?
>
> > I have a Table1 which being dynamic can sometimes end up with now
> > columns what so ever. I'm using Table1 in one of my views along with 2
> > other tables and I would like put a condition at the...something like
> > AND Table1 IS NOTEMPTY
>
> AND EXISTS (SELECT * FROM Table1)
>
> The EXISTS / NOT EXISTS are part of the essential SQL anyone who is
> working with SQL should learn. The above example is simple, but say
> you want all customers who have placed an order / who have never placed
> an order:
>
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> WHERE /* NOT */ EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
>
> You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
> as you get problem if you have a multi-column key. Plus that NOT IN
> can trap you if NULL values are involved.
>
> The query above runs in the Northwind database, if you want to try it.
Oh that's good, thanks! :-)
....and if I wanted to say make sure in a table where normaly there
should be 2000 rows, there are at least 1500 present could I just
simply do the following...or is there a better way?
AND ((SELECT COUNT(*) FROM TABLE1) > 1500)
what do you think of that?
Cheers
Yas
Yas (yasar1@gmail.com) writes:
> Oh that's good, thanks! :-)
> ...and if I wanted to say make sure in a table where normaly there
> should be 2000 rows, there are at least 1500 present could I just
> simply do the following...or is there a better way?
>
> AND ((SELECT COUNT(*) FROM TABLE1) > 1500)
>
> what do you think of that?
That would be the way to do it. It looks like a quite odd condition,
though.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Erland Sommarskog
news:Xns9990ED010F0E2Yazorman@127.0.0.1:
> You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
> as you get problem if you have a multi-column key. Plus that NOT IN
> can trap you if NULL values are involved.
Erland,
Could you elaborate on this or point me to an appropriate web page, please.
TIA
Chris
Chris.Cheney (Chris.CheneyXXNOSPAMXX@tesco.net) writes:
> Erland Sommarskog
> news:Xns9990ED010F0E2Yazorman@127.0.0.1:
>
>> You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
>> as you get problem if you have a multi-column key. Plus that NOT IN
>> can trap you if NULL values are involved.
>
> Erland,
>
> Could you elaborate on this or point me to an appropriate web page,
> please.
I assume that you mean the point on NULL values.
Consider the script below. There are two tables, and we want to find
customers that are not known to be from outside the European Union. If you
run it, you will notice that the two queries return different results;
the query with NOT EXISTS returns one more row.
Maybe not the best example, but it's quite common to see questions on
the newsgroups where people ask why their NOT IN query does not return the
expected result, and these issues are almost always resolved with using
NOT EXISTS. Since I almost never use NOT IN myself, I have not paid
attention to what queries they really write.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Erland Sommarskog (esquel@sommarskog.se) writes:
> Consider the script below.
Eh, what about also including the script:
CREATE TABLE countries
(coucode char(2) NOT NULL PRIMARY KEY,
couname varchar(30) NOT NULL,
is_eu bit NOT NULL)
INSERT countries (coucode, couname, is_eu)
EXEC('SELECT ''FI'', ''Finland'', 1;
SELECT ''SE'', ''Sweden'', 1;
SELECT ''NO'', ''Norway'', 0;
SELECT ''DK'', ''Denmark'', 1;
SELECT ''FO'', ''Faroe Islands'', 0;
SELECT ''IS'', ''Iceland'', 0;')
CREATE TABLE customers (custid int NOT NULL PRIMARY KEY,
name varchar(40) NOT NULL,
coucode char(2) NULL
REFERENCES countries(coucode))
INSERT customers (custid, name, coucode)
EXEC('SELECT 1, ''Pekka Hietaniemi'', ''FI'';
SELECT 2, ''Sven Svensson'', ''SE'';
SELECT 3, ''Geir Lindstad'', ''NO'';
SELECT 4, ''Anders And'', ''DK'';
SELECT 5, ''V U Hammerscheimb'', ''FO'';
SELECT 6, ''Einar Pálsson'', ''IS'';
SELECT 7, ''Krzystof Cibulski'', NULL;')
go
SELECT *
FROM customers
WHERE coucode NOT IN (SELECT coucode
FROM countries
WHERE is_eu = 0)
SELECT *
FROM customers cst
WHERE NOT EXISTS (SELECT *
FROM countries cou
WHERE cst.coucode = cou.coucode
AND cou.is_eu = 0)
go
DROP TABLE customers
DROP TABLE countries
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Erland Sommarskog
news:Xns999191FD3D4DCYazorman@127.0.0.1:
> Erland Sommarskog (esquel@sommarskog.se) writes:
>> Consider the script below.
Many thanks. Now understood.
Chris