There is number of tables and want to calculate total number of records in all tables.
Let us say table 1, table2, table3.
How to display Number of Records (table1 + table2+table3)..? How to achieve this?
You can try by executing the following simple scripts.
Create table #emp1(empid int, empname varchar(30))
Create table #emp2(empid int, empname varchar(30))
Insert into #emp1(empid,empname) values (1,'Rishi')
Insert into #emp1(empid,empname) values (2,'Ram')
Insert into #emp1(empid,empname) values (1,'Ragav')
Insert into #emp1(empid,empname) values (2,'Rishi')
Insert into #emp1(empid,empname) values (3,'Srikar')
Insert into #emp1(empid,empname) values (4,'Tapas')
SELECT * FROM #emp1.
The output is as shown in the below. Total records are 5.
Now insert some values in table emp2
Insert into #emp2(empid,empname) values (2,'Rishi')
Insert into #emp2(empid,empname) values (3,'Srikar')
Insert into #emp2(empid,empname) values (4,'Tapas')
SELECT * FROM #emp2.
The output is as shown in the below. Total records are 3.
Declare @store int
SET @store = 0
Set @store = @store+(Select COUNT(*) from #emp1)
Set @store =@store+(Select COUNT(*) from #emp2)
Print @store
Alternate Script by using SELECT
Declare @store int
SET @store = 0
Set @store = @store+(Select COUNT(*) from #emp1)
Set @store =@store+(Select COUNT(*) from #emp2)
SELECT @store as Totcount
It displays Totcount as 8 records
How to display duplicate records ….?
Let us assume there is a table #emp which contains some duplicate records as shown below:
Here Rishi,Srikar,Tapas are duplicate records. I want to display these 3 records only.
SELECT * FROM #emp
GROUP BY empid,empname
HAVING COUNT(*) > 1
The following query retrieves only NO Duplicate Records.
SELECT * FROM #emp where empid NOT IN (
SELECT empid FROM #emp
GROUP BY empid
HAVING COUNT(*) > 1)