Thursday 27 June 2013

Get month wise Calender for given year

                  Hi,
                                  In this article we will see how to get calender of all month in given year.
in privious article i have discused about get calender of given month of current year.


Solution::

1) Create below procedures ::


CREATE PROCEDURE [dbo].[CALENDAR] (@YEAR int) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=1
WHILE @STARTMONTH<=12
BEGIN
SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATENAME(WEEK,@DATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
WHILE @wkcount<= @weeksinmonth
begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
SET @WKCOUNT=@WKCOUNT + 1
end
WHILE @COUNT< =@MONTHDAYCOUNT
BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1

END

SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)
SET @COUNT=@COUNT + 1
END
UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
SELECT SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY FROM #TEMP
DROP TABLE #TEMP
SET @INPUTDATE=DATEADD(MM,1,@INPUTDATE)
SET @STARTMONTH=@STARTMONTH+1
END


2)Test Case::

exec CALENDAR 2013

Friday 21 June 2013

Insert output of sql function to table

In this article we will see how to insert data or records from sql table value function .

example::

1)Create table::
Table1:


Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')

Table2:

Create table #temp (id int ,emp_name varchar(100))

2)Create Function::


CREATE FUNCTIONSql_fun
(
)
RETURNS TABLE
AS
RETURN (
select id,emp_name from emp
)
GO

3)insert from sql function to table::


insert into #temp (cp_code,cp_name)select * from Sql_fun()
select * from #temp
Related Posts
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

Insert store procedure output to sql table

In this article we will see how to insert store procedure output to sql table.
example::
1)Create table::
A)

Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')

B)
Create table #temp (id int ,emp_name varchar(100))

2)Create procedure::


create proc proc_out
as
begin
select id,emp_name from emp
end

3)Insert output of proc to table ::


insert into #temp (cp_code,cp_name)exec cp
select * from #temp
Related Posts
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

Insert bulk data from view to sql table

In this article we will see how to insert bulk data from view to sql table.

example::

1)Create table::

Table1:


Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')

Table2:

Create table #temp (id int ,emp_name varchar(100))

2)Create View::


create view sql_view
as
select id,emp_name from emp

3)insert from sql View to table::



insert into #temp (id,emp_name)select * from sql_view
select * from #temp

Related Posts

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

bulk insert from one table to another table

In this article we will see how to insert store procedure output to sql table.
example::
1)Create table::
A)

Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')

B)
Create table #temp (id int ,emp_name varchar(100))


2)insert bulk data from one table to another by using select .

insert into #temp (id,emp_name)select id,emp_name from emp
select * from #temp


3)insert bulk data from one table to another by using select with where clouse.

insert into #temp (id,emp_name)select id,emp_name from emp where emp_name='pranav'
select * from #temp


4)Create copy of table with data ,or create backup of table.
select id,emp_name into #temp1 from emp
select id,emp_name into #temp2 from emp where emp_name='pranav'

Related Posts

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

Monday 17 June 2013

pivot without aggregate function in sql server

       Hi ,
                 In this article we will see how to do pivot without aggregate function.

Example::

1)Create table and fill with data::


Create table #Unpivot (date varchar(100) ,day_name Varchar(100))
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+1),'Tuesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+2),'Wednesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+3),'Thursday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+4),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+5),'Tuesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+6),'Wednesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+7),'Thursday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+8),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+9),'Tuesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+10),'Wednesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+11),'Thursday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+12),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+13),'Tuesday')

2) Check below query for pivot data without aggregate and with Cursor.


select date,day_name from #Unpivot
declare @count int
select @count=count(*) from #Unpivot
create table #pivot(Monday datetime,
Tuesday datetime,
Wednesday datetime,
Thursday datetime
)
declare @flag varchar(100)
declare @str varchar(max)
declare @date varchar(100)
declare @date_name varchar(50)
set @flag ='First_Time'
set @str =''
declare @end int
set @end=0
declare @pivotdata CURSOR
set @pivotdata = CURSOR for
select date,day_name from #Unpivot
open @pivotdata
fetch next
from @pivotdata into @date,@date_name
while @@FETCH_STATUS = 0
begin
set @end=@end+1
if @flag ='First_Time'
begin
if(@date_name='Monday')
begin
set @str =@date
end

if(@date_name='Tuesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@date_name='Wednesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@date_name='Thursday')
begin
if @str=''
begin
set @str ='0'+','+'0'+','+'0'+@date
end
else
begin
set @str =@str+','+@date
end
insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data
set @str=''
set @flag='0'
end
end
else
begin

if(@date_name='Monday')
begin
set @str =@date
if @end =@count
begin

insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data
end
end
if(@date_name='Tuesday')
begin
set @str =@str+','+@date
if @end =@count
begin

insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data

end
end

if(@date_name='Wednesday')
begin
set @str =@str+','+@date
if @end =@count
begin
insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data

end
end
if(@date_name='Thursday')
begin

set @str =@str+','+@date

insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data
set @str=''
end
end
fetch next
from @pivotdata into @date,@date_name
end
close @pivotdata
deallocate @pivotdata

3) Check output::

select * from #pivot

loop in sql server

In this article we well discuss about a WHILE loop.

1) What is while loop ? and what is difference between while loop and cursor ?

A) While loop ::

Sets a condition for the repeated execution of an SQL statement or statement block.
The statements are executed repeatedly as long as the specified condition is true.
The execution of statements in the WHILE loop can be controlled from inside the
loop with the BREAK and CONTINUE keywords.

B) Cursor ::
A cursor is a set of rows together with a pointer that identifies a current row.
In other word, Cursor is a database object used by applications to manipulate data
in a set on a row-by-row basis, its like record-set in the ASP and visual basic.


Syntax of while loop::
while (Condition)
begin
-- write your code here
end
go

1)Simple example of while loop::


DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=12)
BEGIN
PRINT 'you come in loop for ' + Convert(varchar, @intFlag ) + ' times'
SET @intFlag = @intFlag + 1
END
GO


2) Example of WHILE Loop with BREAK keyword

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT 'you come in loop for ' + Convert(varchar, @intFlag ) + ' times'
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO


3) Example of WHILE Loop with CONTINUE and BREAK keywords


DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT 'you come in loop for ' + Convert(varchar, @intFlag ) + ' times'
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

Get MonthWise Calender in sql server

hi,
                              In this article we will see,how to get month wise calender in tabular format.
in rare cases we want do this.

1) Create below procedure::

Create proc get_month

@month int
as
begin
declare @newdate datetime
declare @year varchar(50)
select @year=DateName(year,getdate());;
if len(@month)=1
begin
SELECT @newdate = convert(DATETIME,Convert(varchar, Convert(varchar,@year)+'0'+ Convert(varchar,@month)+'15'), 115)
end
else
begin

SELECT @newdate = convert(DATETIME,Convert(varchar, Convert(varchar,@year)+Convert(varchar,@month)+'15'), 115)

end

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SELECT @endDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@newdate))),DATEADD(mm,1,@newdate)),101)
print @endDate
SELECT @startDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@newdate)-1),@newdate),101);
print @startDate;

WITH dates(Date) AS
(
SELECT @startdate as Date
UNION ALL
SELECT DATEADD(d,1,[Date])
FROM dates
WHERE DATE < @enddate
)
SELECT Date into #temp
FROM dates
OPTION (MAXRECURSION 0)

--select * from #temp

declare @end int
select @end=DateName(day,@endDate)

create table #data (Monday varchar(50),
Tuesday varchar(50),
Wednesday varchar(50),
Thursday varchar(50),
Friday varchar(50),
Saturday varchar(50),
Sunday varchar(50))
declare @date varchar(100)
declare @datename varchar(100)
declare @flag varchar(100)
declare @str varchar(max)
set @flag ='sandip'

set @str=''
declare @getdateName CURSOR
set @getdateName = CURSOR for
select date from #temp
open @getdateName
fetch next
from @getdateName into @date
while @@FETCH_STATUS = 0
begin
if @flag='sandip'
begin
print 'sandip'
select @datename= DateName(weekday,@date)
select @date=DateName(day,@date)

PRINT @datename
PRINT @date
if(@datename='Monday')
begin
set @str =@date
end


if(@datename='Tuesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Wednesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+@date

END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Thursday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Friday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+'0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end

if(@datename='Saturday')
begin

IF @str =''
BEGIN
set @str ='0'+','+'0'+','+'0'+','+'0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@datename='Sunday')
begin

if @str=''
begin
print 'end'
set @str ='0'+','+'0'+','+'0'+','+'0'+','+'0'+','+'0'+','+@date
end
else
begin
set @str =@str+','+@date
end

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
set @str=''
set @flag='0'
end
end
else
begin
select @datename= DateName(weekday,@date)
select @date=DateName(day,@date)
if(@datename='Monday')
begin
set @str =@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
end


end
if(@datename='Tuesday')
begin
set @str =@str+','+@date
if @end =@date
begin
insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
end
end
if(@datename='Wednesday')
begin
set @str =@str+','+@date
if @end =@date
begin
insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data

end
end

if(@datename='Thursday')
begin
set @str =@str+','+@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data

end
end

if(@datename='Friday')
begin
set @str =@str+','+@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data

end
end
if(@datename='Saturday')
begin


set @str =@str+','+@date
if @end =@date
begin

insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
end
end
if(@datename='Sunday')
begin
set @str =@str+','+@date
print @str
insert into #data (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3],[4],[5],[6]) )data
set @str=''
end
end
fetch next
from @getdateName into @date
end
close @getdateName
deallocate @getdateName
select * from #data
end

test case::

exec get_month 12

temp table and temp variable in sql server

                                        In this article we will see how to create temp table and table variable.
what is difference in both.As a rule of thumb, for small to medium volumes of data and
simple usage scenarios you should use table variables.
1)              Temporary Tables are real tables so you can do things like CREATE INDEXes,
etc. If you have large amounts of data for which accessing by index will be faster then
temporary tables are a good option. with temp variablewe have not possible to use or
create indexes.

2)   Table variables dont participate in transactions, logging or locking. Temporary Tables
participate in transactions,logging or locking.
3)   You can create a temp table using SELECT INTO you can not create table variable
using select into.
4)   Both table variables and temp tables are stored in tempdb.
5)   Global Temp Tables (##tmp) are another type of temp table available to all sessions and
users. a table variable is available in currect session only.

6)Syntax of table variable::


declare @table_name as table (column_name datatype)

7)Syntax of Temporary Table::


a)local temp table ::

create table #table_name(column_name datatype)

b)global temp table ::

create table ##table_name(column_name datatype)

8)examples::


A)Table variable ::


declare @temp_table as table (id int ,s_name varchar(50))
insert into @temp_table (id,s_name) values(1,'sandipG')
select * from @temp_table

B)local temp table ::


create table #temp(temp_id int,temp_name varchar(50))
insert into #temp (id,s_name) values(1,'sandipG')
select * from #temp

C)global temp table ::


create table ##temp(temp_id int,temp_name varchar(50))
insert into ##temp (id,s_name) values(1,'sandipG')
select * from ##temp


Saturday 15 June 2013

update data in view with multiple table

Hi,
      In privious article I have discussed about Basic of view . Now we will discuss about a how
to create a view with multiple table and how to update data or records in tables by using view.

example::


create table emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int) create table dept_master (dept_id int identity(1,1),dept_name varchar(100))

Insert records in table::



insert into emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)
insert into dept_master (dept_name ) values('admin')
insert into dept_master (dept_name ) values('IT')
insert into dept_master (dept_name ) values('production')

1)Create view with multiple table with select all columns in table .


alter view [dbo].emp_dept
as
SELECT dbo.emp_master.emp_name,
dbo.emp_master.emp_sal,dbo.dept_master.dept_name, dbo.dept_master.dept_id AS dept_id, dbo.emp_master.emp_id

FROM dbo.emp_master INNER JOIN
dbo.dept_master ON dbo.emp_master.dept_id = dbo.dept_master.dept_id

it is not possible to exicute a update statement againests view with multiple tables. you must
be write a trigger on that view for update records.
Create  TRIGGER dbo.Update_view
ON emp_dept
instead of update
AS
BEGIN
SET NOCOUNT ON;
declare @dept_name varchar(100);
declare @empname varchar(100);
declare @emp_sal varchar(100);
declare @dept_id varchar(100);
declare @emp_id varchar(100);

select @dept_name=i.dept_name from inserted i;
select @dept_id=i.dept_id from inserted i;

update dept_master set dept_name=@dept_name where dept_id=@dept_id

select @emp_id=i.emp_id from inserted i;
select @empname=i.emp_name from inserted i;
select @emp_sal=i.emp_sal from inserted i;

update emp_master set emp_name=@empname,emp_sal=@emp_sal,dept_id =@dept_id where emp_id= @emp_id
END
GO

Test Case::

update emp_dept set emp_name ='sachin',emp_sal=2123 ,dept_name='Computer' where dept_id=1 and emp_id=1 select * from emp_master select * from dept_master

Update view with single table

Hi,
      In privious article I have discussed about Basic of view . Now we will discuss about a how
to create a view on single table and how to update data or records in table by using view.
example::

create table emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)

Insert records in table::


insert into emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)


1)Create view on single table with select all columns in table .

Create view [dbo].View_on_single_table_1
as
select * from emp_master


Test Case 1::

update View_on_single_table_1 set emp_name ='sachin',emp_sal='2123',dept_id=5 where emp_name='sandipG'
select * from emp_master


Related Posts::

basic of view
insert into view with multiple tables Insert into view with single table Instead of update trigger and after update trigger in sql
Instead of Insert trigger and after insert trigger in sql
instead of delete trigger and after delete trigger in sql
basic of trigger and interview quetions on trigger

Group by in sql server

  Hi,
                    In this article we will see how to use simple group by clouse,group by with having,
rollup and compute.
Create below table and fill some data in that table .


Create table #ExamResult(name varchar(50),Subject varchar(20),Marks int)


insert into #ExamResult values('Adam','Maths',70)
insert into #ExamResult values ('Adam','Science',80)
insert into #ExamResult values ('Adam','Social',60)

insert into #ExamResult values('Rak','Maths',60)
insert into #ExamResult values ('Rak','Science',50)
insert into #ExamResult values ('Rak','Social',70)

insert into #ExamResult values('Sam','Maths',90)
insert into #ExamResult values ('Sam','Science',90)
insert into #ExamResult values ('Sam','Social',90)
insert into #ExamResult values ('Sam','Social',80)

step 1:: Simple group by Clouse


select name ,Subject,sum(Marks) total_mark from #ExamResult group by name,subject order by name,subject
step2:: Group by with having


select Subject,sum(Marks) total_mark from #ExamResult group by subject having subject='Social' order by subject
step3:: Group by with roll up


select name ,Subject,sum(Marks) total_mark from #ExamResult group by name,subject with rollup
step4:: Group by with compute function


select name ,Subject,Marks from #ExamResult
compute sum(Marks)

Thursday 13 June 2013

Interview queries in sql server

    Hi
           In this article I write a some queries which is frequently asked during interviews for sql.
For more queries check my privious post interview queries for sql server
here i have discussed about those queries which is i faced during interviews.
if you have any queries ,questions ,problems then please send me by email or comments.

for your better understanding ,Create below table and fill some record in this.

create table #emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)

insert into #emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into #emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into #emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into #emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)

SQL QUERIES ::

1)delete duplicate records from table



delete from #emp_master where emp_id not in (select min(emp_id ) from #emp_master group by emp_name )

2)select 2nd highest salary



select max(Convert(int, emp_sal))sal from #emp_master where emp_sal not in(select max(Convert(int, emp_sal)) from #emp_master)

3) select N th highest salary



select max(Convert(int, emp_sal))sal from #emp_master where Convert(int, emp_sal) not in (select top n emp_sal from #emp_master order by Convert(int, emp_sal) desc)

4) select 3 rd highest salary from #emp_master



select max(Convert(int, emp_sal)) sal from #emp_master where Convert(int, emp_sal) not in (select top 2 emp_sal from #emp_master order by Convert(int, emp_sal) desc)

5) Add String with any column



select emp_name +' '+'SandipG-sql' names from #emp_master

6) add static column in select statement



select emp_name ,'SandipG-sql' MYID from #emp_master

7) select name with upper case and lower case



select upper(emp_name) emp_up ,lower(emp_name) emp_lo from #emp_master

8) Charindex function in sql server



select charindex('/','dfsdfs/dfs')
select charindex('s','dfsdfs/dfs')

All about Sql Server: insert into view with multiple tables

All about Sql Server: insert into view with multiple tables: Hi,        In privious article I have discussed about Basic of view and View on single table. Now we will discuss ...

Wednesday 12 June 2013

insert into view with multiple tables

Hi,
       In privious article I have discussed about Basic of view and View on single table. Now we
will discuss about a how to create a view on multiple table and how to insert data or records in
all tables by using view.

example::

Create table::


create table emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)
create table dept_master (dept_id int identity(1,1),dept_name varchar(100))
_

Insert records in table::


insert into emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)
insert into dept_master (dept_name ) values('admin')
insert into dept_master (dept_name ) values('IT')
insert into dept_master (dept_name ) values('production')

Create view on multiple tables


Create view [dbo].emp_dept
as
SELECT dbo.dept_master.dept_id AS dept_id, dbo.emp_master.emp_id, dbo.emp_master.emp_name, dbo.emp_master.emp_sal,
dbo.dept_master.dept_name
FROM dbo.emp_master INNER JOIN
dbo.dept_master ON dbo.emp_master.dept_id = dbo.dept_master.dept_id

Test Case1::Try to insert data in view on multiple tables


insert into emp_dept (emp_name, emp_sal,dept_name)values('sachin','2123','cleaner')
select * from emp_master
select * from dept_master

you get error when you try to insert by using above statement.For solve this problems you
must be write a trigger on view.

Create trigger for insert data in multiple tables by view

Create TRIGGER dbo.Insert_view
ON emp_dept
instead of INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @dept_name varchar(100);
declare @empname varchar(100);
declare @emp_sal varchar(100);
select @dept_name=i.dept_name from inserted i;
declare @dept_id int
insert into dept_master (dept_name) values(@dept_name)
SELECT @dept_id=@@IDENTITY
select @empname=i.emp_name from inserted i;
select @emp_sal=i.emp_sal from inserted i;
insert into emp_master (emp_name,emp_sal,dept_id ) values(@empname,@emp_sal,@dept_id)
END
GO

Now you can try to do this.


insert into emp_dept (emp_name, emp_sal,dept_name)values('sachin','2123','cleaner')
select * from emp_master
select * from dept_master

Related links ::
basic of view
update trigger
Insert trigger
delete trigger
basic of trigger

Insert into View in sql server

Hi,
      In privious article I have discussed about Basic of view . Now we will discuss about a how
to create a view on single table and how to insert data or records in table by using view.
example::

Create table::

create table emp_master (emp_id int identity(1,1) ,emp_name varchar(100),emp_sal varchar(100) ,dept_id int)

Insert records in table::


insert into emp_master (emp_name,emp_sal,dept_id ) values('sandipG','1000',1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('ajit','45321' ,1)
insert into emp_master (emp_name,emp_sal,dept_id ) values('pranav','5000',2)
insert into emp_master (emp_name,emp_sal,dept_id ) values('aditya','10000',3)


1)Create view on single table with select all columns in table .

Create view [dbo].View_on_single_table_1
as
select * from emp_master

Test Case::

insert into View_on_single_table_1 (emp_name,emp_sal,dept_id)values('sachin','2123',5) select * from emp_master

2)Create view on single table with select Two columns in table .


Create view [dbo].View_on_single_table_2
as
select emp_name,emp_sal from emp_master

Test Case 1::

insert into View_on_single_table_2 (emp_name,emp_sal,dept_id)values('sachin','2123',5)
select * from emp_master

you Get error when you try to exicute above statement becouse you are not
selected dept_id in view .


Test Case 2::


insert into View_on_single_table_2 (emp_name,emp_sal)values('sachin','2123')
select * from emp_master

Related links ::
basic of view
update trigger
Insert trigger
delete trigger
basic of trigger

Friday 7 June 2013

Trigger on Update


Hi,
            In previous article we will see what is trigger? and types of trigger? , example of after
insert ,and instead of insert trigger now in this article we will see how to write a trigger after
update and instead of update trigger.

Create a two tables for our better understanding by using below query.


Create table temptrigger ( id varchar(100) ,sname varchar(100))
create table TRIGGER_data ( id varchar(100) ,sname varchar(100))

A)AFTER update Trigger::


Create TRIGGER after_update ON [dbo].[temptrigger]
FOR update
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from inserted i;
select @empname=i.sname from inserted i;
insert into TRIGGER_data
(id,sname)
values(@empid,@empname);
PRINT 'AFTER update trigger fired.'
GO

Check OUTPUT::


insert into temptrigger (id,sname )values(1,'san')
update temptrigger set id=2 ,sname='name' where id=1
select * from temptrigger
select * from TRIGGER_data

B)INSTEAD OF Update Trigger::


CREATE TRIGGER instead_update ON [dbo].[temptrigger]
instead of update
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from inserted i;
select @empname=i.sname from inserted i;
insert into TRIGGER_data
(id,sname)
values(@empid,@empname);
PRINT 'AFTER INSERT trigger fired.'
GO

Check OUTPUT::


insert into temptrigger (id,sname )values(1,'san')
update temptrigger set id=2 ,sname='name' where id=1
select * from temptrigger
select * from TRIGGER_data

Trigger on insert


Hi,
In previous article we will see what is trigger? and types of trigger? ,example of Trigger on update
,and trigger on delete .now in this article we will see how to write a trigger after insert and
instead of insert trigger.

Create a two tables for our better understanding by using below query.


Create table temptrigger ( id varchar(100) ,sname varchar(100))
create table TRIGGER_data ( id varchar(100) ,sname varchar(100))

A)AFTER insert Trigger::


Create TRIGGER trg_After_insert ON [dbo].[temptrigger]
FOR insert
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from inserted i;
select @empname=i.sname from inserted i;
insert into TRIGGER_data
(id,sname)
values(@empid,@empname);
PRINT 'AFTER insert trigger fired.'
GO

Check OUTPUT::


insert temptrigger (id,sname)values(1,'sandip')
select * from temptrigger
select * from TRIGGER_data

B)INSTEAD OF insert Trigger::


CREATE TRIGGER trg_insert_instead ON [dbo].[temptrigger]
instead of insert
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from inserted i;
select @empname=i.sname from inserted i;
insert into TRIGGER_data
(id,sname)
values(@empid,@empname);
PRINT 'INSTEAD OF insert Trigger.'
GO

Check OUTPUT::


insert temptrigger (id,sname)values(1,'sandip')
select * from temptrigger
select * from TRIGGER_data

trigger on delete


Hi,
                 In previous article we will see what is trigger? and types of trigger? , example of
after update ,and instead of update.and example of after insert and instead of insert. now in
this article we will see how to write a trigger after delete and instead of delete trigger.

Create a two tables for our better understanding by using below query.


Create table temptrigger ( id varchar(100) ,sname varchar(100))
create table TRIGGER_data ( id varchar(100) ,sname varchar(100))

A)AFTER delete Trigger::


Create TRIGGER trg_After_delete ON [dbo].[temptrigger]
FOR delete
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from deleted i;
select @empname=i.sname from deleted i;
insert into TRIGGER_data(id,sname) values(@empid,@empname);
PRINT 'AFTER delete trigger fired.'
GO

Check OUTPUT::


insert into temptrigger (id,sname )values(1,'san')
delete temptrigger where id=1
select * from temptrigger
select * from TRIGGER_data

B)INSTEAD OF delete Trigger::


CREATE TRIGGER trg_delete_instead ON [dbo].[temptrigger]
instead of delete
AS
declare @empid int;
declare @empname varchar(100);
select @empid=i.id from deleted i;
select @empname=i.sname from deleted i;
insert into TRIGGER_data(id,sname)values(@empid,@empname);
PRINT 'instead delete trigger fired.'
GO

Check OUTPUT::


insert into temptrigger (id,sname )values(1,'san')
delete temptrigger where id=1
select * from temptrigger
select * from TRIGGER_data

basic of trigger

1)What is Trigger?

A trigger is a special kind of a store procedure that executes in response to certain action
on the table like insertion, deletion or updation of data. It is a database object which is bound
to a table and is executed automatically. You can’t explicitly invoke triggers.
The only way to do this is by performing the required action no the table that they are assigned to.


2) Types of Trigger

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE.
So, there are three types of triggers and hybrids that come from mixing and matching the events and
timings that fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers
(ii) Instead Of Triggers


(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views.
after triggers can be classified further into three types as:


(a) After insert Trigger.


This type of trigger is fired after insert statement.

(b) After update Trigger.

This type of trigger is fired after update statement.

(c) After delete Trigger.

This type of trigger is fired after delete statement.

(ii) Instead Of Triggers

This type of trigger is fired inplace of triggering action .They are supported for views.
e.g :if you write a instead of insert trigger ,and if you try to insert records on that table then .
inplace of insert statement a trigger is fired .

instead of Triggers can be classified further into three types as:-


(a) instead of insert Trigger.

This type of trigger is fired inplace of insert statement.

(b) instead of update Trigger.

This type of trigger is fired inplace of update statement.

(c) instead of delete Trigger.

This type of trigger is fired inplace of delete statement.

Thursday 6 June 2013

Interview:: Sql queries for sql server

Interview Queries::
    Hi
           In this article I write a some queries which is frequently asked during interviews for sql.
here i have discussed about those queries which is i faced during interviews.
if you have any queries ,questions ,problems then please send me by email or comments.

for your better understanding ,Create below table and fill some record in this.

table1 ::


create table #emp_master (row_id int ,emp_name varchar(100),salary int,dept_no int )

insert into
#emp_master (row_id ,emp_name ,salary ,dept_no)values (1,'sandip',5000,1)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (2,'aditya',2000,1)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (3,'pranav',3000,1)

insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (4,'savita',5000,2)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (5,'neha',500,2)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (6,'dipali',1500,2)

insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (7,'ajit',50002,3)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (8,'anukrushna',10000,3)
insert into #emp_master (row_id ,emp_name ,salary ,dept_no)values (9,'rao',1500,3)


table2 ::


create table dept (dept_id int,dept_name varchar(100))
insert into dept (dept_id ,dept_name ) values(1,'IT')
insert into dept (dept_id ,dept_name ) values(2,'admin')
insert into dept (dept_id ,dept_name ) values(3,'board')
insert into dept (dept_id ,dept_name ) values(4,'Production')
insert into dept (dept_id ,dept_name ) values(5,'Cleaner')


SQL QUERIES ::

1) Count the totalsalary deptno wise where more than 2 employees exist.



SELECT dept_no, sum(salary) As totalsal FROM #emp_master GROUP BY dept_no HAVING COUNT(emp_name) > 2

2) To fetch ALTERNATE records from a table. (ODD NUMBERED)



select * from #emp_master where row_id in (select row_id from #emp_master where row_id%2>0)

3) To select ALTERNATE records from a table.(EVEN NUMBERED)



select * from #emp_master where row_id in (select row_id from #emp_master where row_id%2=0)

5) select only one records with 3 max salary



select top 1 * from (select top 3 row_id ,emp_name ,salary ,dept_no from #emp_master order by salary desc)tab order by salary asc

6) select all records with 3 min salary



select top 1 * from (select top 3 row_id ,emp_name ,salary ,dept_no from #emp_master order by salary desc)tab order by salary asc

7) select only one records with 3 min salary



select top 1 * from (select top 3 row_id ,emp_name ,salary ,dept_no from #emp_master order by salary asc)tab order by salary desc

8) Select FIRST n records from a table.



select top(n) * from #emp_master
select top(3) * from #emp_master

9) Select LAST n records from a table



select * from #emp_master where row_id not in
(select top ((select count(*) from #emp_master)-3) row_id from #emp_master)

10) List dept no., Dept name for all the departments in which there are no employees in the
department.



select * from dept where dept_id not in (select dept_no from #emp_master)

11) How to get 3 Max salaries ?



select distinct top 3 * from #emp_master order by salary desc

12) How to get 3 Min salaries ?



select distinct top 3 salary from #emp_master order by salary asc

13) Select DISTINCT RECORDS from emp table.



select distinct * from #emp_master

14) How to delete duplicate rows in a table?



15) Count of number of employees in department wise.



select Count(emp_name) no_of_emp, dept_name from #emp_master inner join dept on dept_id=dept_no group by dept_name

16) Suppose there is annual salary information provided by emp table. How to fetch monthly
salary of each and every employee?



select ename,salary/12 as monthlysal from #emp_master

17)  Select all record from emp table where deptno =1 or 3.



select * from #emp_master where dept_no=3 or dept_no=1
alternate solutions
select * from #empmaster where dept_no in (3,1)

18) Select all record from emp table where deptno=3 and sal>1500.



select * from #emp_master where dept_no=3 and salary>1500

19) Select all records where ename starts with ‘S’ and its lenth is 6 char.



select * from #emp_master where emp_name like'S_____'

20) Select all records where ename may be any no of character but it should end with ‘p’.



select * from #emp_master where emp_name like'%p'

21) How can I create an empty table emp1 with same structure as emp?



select * into #emp1 from #emp_master where 1=2; -- with data
select * into #emp2 from #emp_master where 1=1; -- with all data

22) How to retrive record where sal between 1000 to 2000?



select * from #emp_master where salary between 1000 and 2000

23) If there are two tables #emp_master and emp1, and both have common record.
How can I fetch all the recods but common records only once?



(Select * from #emp_master) Union (Select * from #emp1)

24) How to fetch only common records from two tables emp and #emp2?



(Select * from #emp_master) Intersect (Select * from #emp2)

25) How can I retrive all records of #emp_master those should not present in emp2?



Select * from #emp_master where emp_name not in (Select emp_name from #emp2)

26) Select salary less than 3000 from emp table.



select * from #emp_master where salary<3000 data-blogger-escaped-div="">

27) Select all the employee group by deptno and sal in descending order.



select emp_name,salary,dept_no from #emp_master order by dept_no, salary desc