How to display total counts sum of two tables and How to display duplicate recs

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. 

Now total count of the records = Count of the records in #emp1 + Count of the records in #emp2

 

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)

 

Difference Between SET and SELECT

Difference Between SET and SELECT

1.            SET is the ANSI standard for variable assignment, SELECT is not.

2.            SET can only assign one variable at a time, SELECT can make multiple assignments at once.

3.            If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)

4.            When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)

5.            As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

How to create multiple views at a time ?

Here is one Scenerio. Number of tables to be converted into views.

I have 800 tables. For each table, I have to create one view.  For example there is a table called EMP. It contains the following columns.

-          EmpId

-          EmpName

-          Dept

We can obtain the view when we execute the following script

CREATE VIEW [dev].[vwEmp]
AS
SELECT  [EmpID]
        ,[EmpName]
        ,[vwEmp]
FROM [Dev].[Emp]

Like that i have to create views for 800 tables.

Here I provide a simple script which can convert all the tables into views. You can modify this script with respect to your requirements and needs. Take care about Server, DB and Schema while u code.

USE [Your_Database]

GO

DECLARE col_csr CURSOR FOR

     Select c.table_name

            , c.table_schema

            , c.column_name

     From information_schema.tables t

     inner join information_schema.columns c on c.table_name = t.table_name

     where t.table_type = 'BASE TABLE'

     order by c.table_name, c.ordinal_position

    

declare @tname nvarchar(128), @sname nvarchar(128), @cname nvarchar(128)

declare @prev_tname nvarchar(128), @prev_sname nvarchar(128)

OPEN col_csr

FETCH NEXT FROM col_csr into @tname, @sname, @cname

WHILE @@FETCH_STATUS = 0

BEGIN

    if @prev_tname <> @tname begin

        print ' FROM [' + db_name() + '].[' + @prev_sname + '].[' + @prev_tname + ']'

        print 'GO'

        print ' '

       

        set @prev_tname = null

    end

   

    if @prev_tname is null begin

        set @prev_sname = @sname

        set @prev_tname = @tname

   

        print 'CREATE VIEW [' + @sname + '].[vw' + @tname + ']'

        print 'AS'

        print 'SELECT [' + @cname + ']'

    end else begin

        print '      ,[' + @cname + ']'

    end   

   

    FETCH NEXT FROM col_csr into @tname, @sname, @cname

END

print ' FROM [' + db_name() + '].[' + @prev_sname + '].[' + @prev_tname + ']'

print 'GO'

CLOSE col_csr

DEALLOCATE col_csr

GO