Loading data from excel file to table in SQL Server

Create a table structure with appropriate datatypes according to your data in a file.
Save file in .csv format. Use below script to load data.
Note: You may get errors if you use in appropriate datatypes or size.

BULK INSERT EMP -- Table Name
FROM ‘C:\’ -- File Path
WITH ( FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)

How to diplay a message when no records found in a query

If there are no records in a query result it will display nothing.
If client says that a message should be displayed if there are no records with in the query result ,
We can achive it easily in BI Publisher. But if you want to do it in a query it self.

In the below example there are no records with a criteria deptno=50.

So you will get a result with  'no datafound'.


With X as           
(select deptno,'' dummy from emp where deptno=50)
select deptno,dummy from X
union
select NULL,'no datafound' from dual where not exists(select * from X)

How to display Categories with no records in a query

 In the below example we have dept numbers 10,20,30 only in emp table.
when we try to count number of records in each dept we get only 10,20 and 30.
But the client says that he needs all the dept counts and if there are no records with in the dept 40 and 50 he should get 0 records.

In this situation we can use below query to achieve it. you can also achieve it by creating one dummy table with dummy column which includes all the dept numbers and use outer join with emp table to get this result.

select a.deptnum,nvl(b.cnt,0) from (select '10' deptnum from dual
            union select '20' deptnum from dual
            union select '30' deptnum from dual
            union select '40' deptnum from dual
            union select '50' deptnum from dual)a,(select case when deptno=10 then '10'
            when deptno=20 then '20'
            when deptno=30 then '30'
            when deptno=40 then '40'
            when deptno=50 then '50'
            else 'Others' end deptnum, count(*) cnt from
            emp
            group by
            case when deptno=10 then '10'
            when deptno=20 then '20'
            when deptno=30 then '30'
            when deptno=40 then '40'
            when deptno=50 then '50'
            else 'Others' end) b
            where a.deptnum=b.deptnum(+)