Thursday, 20 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

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 #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

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:

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

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 #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'

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

Friday, 14 June 2013

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

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')

Saturday, 1 June 2013

select duplicate records in sql server

In this article we will see how to select duplicate records by using
Comman table expression and Row_number() function in sql server

Example:

step1: Create a table

   CREATE TABLE #TEMP
   (
   id int
,city VARCHAR(50)
,sNAME VARCHAR(50)

   )

step2: insert data into table

INSERT INTO #TEMP (id,city,SNAME)VALUES(1,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(2,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(3,'sangli','SANDIPg')

   INSERT INTO #TEMP (id,city,SNAME)VALUES(4,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(5,'sangli','SANDIPg')

   INSERT INTO #TEMP (id,city,SNAME)VALUES(6,'kolhapur','SANDIP')

   INSERT INTO #TEMP (id,city,SNAME)VALUES(7,'kolhapur','SANDIP')

INSERT INTO #TEMP (id,city,SNAME)VALUES(8,'kolhapur','SANDIP')

   INSERT INTO #TEMP (id,city,SNAME)VALUES(9,'mumbai','SANDIP')

   INSERT INTO #TEMP (id,city,SNAME)VALUES(10,'mumbai','SANDIP')

step3:: use below query for select a duplicate records from your table

WITH CTE AS
(
select id,city,SNAME, row_number()
over(partition by SNAME order byid desc)
duplicate_id from #TEMP
)

select * CTE where duplicate_id>1

explaination::

I have used a row_number() function for numbering a duplicate records.
for your practice you can try same query for select duplicate cities
In This article I have explain how to select duplicate records from table by using
row_number() function and comman table expression.
i hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion
,Thanks

update duplicate records in sql server

In this article we will see how to update duplicate records by using Comman table expression
and Row_number() function in sql server .

Example:

step1: Create a table

CREATE TABLE #TEMP
(
id int
,city VARCHAR(50)
,sNAME VARCHAR(50)

)

step2: insert data into table

INSERT INTO #TEMP (id,city,SNAME)VALUES(1,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(2,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(3,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(4,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(5,'sangli','SANDIPg')

INSERT INTO #TEMP (id,city,SNAME)VALUES(6,'kolhapur','SANDIP')

INSERT INTO #TEMP (id,city,SNAME)VALUES(7,'kolhapur','SANDIP')

INSERT INTO #TEMP (id,city,SNAME)VALUES(8,'kolhapur','SANDIP')

INSERT INTO #TEMP (id,city,SNAME)VALUES(9,'mumbai','SANDIP')

INSERT INTO #TEMP (id,city,SNAME)VALUES(10,'mumbai','SANDIP')

step3:: use below query for update a duplicate records from your table

WITH CTE AS
(
select id,city,SNAME, row_number()
over(partition by SNAME order byid desc)
duplicate_id from #TEMP
)
updateCTE set SNAME =null whereduplicate_id>1
select * from#temp

explaination::
I have used a row_number() function for numbering a duplicate records.
for your practice you can try same query for update duplicate cities
In This article I have explain how to update duplicate records from table by using
row_number() function and comman table expression.
i hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion
,Thanks

Related Posts::

Comman table expression in sql server
Rank(),dence_rank(),row_number() functions in sql server
cursor in sql server

comman table expression(CTE) in sql server

What is comman table expression ?

1.Common Table Expression is the temporary result set, and it should be with in the execution
of the one SELECT, DELETE, UPDATE, INSERT or Create View statement.
2.It will work during query execution only.
3.This will store as an object in the SQL Server.
4.CTE is the self referencing and could be able to refer multiple time in the same query.

Syntax:

:
WITH Expression_Name [ ( ColumnName [1,...n] ) ]
AS
( CTE query definition )

Usage of CTE:

•We can create a recursive query.
•CTE will be the substitute of VIEW. no need to store definition in the metadata.
•This is enable grouping by a column CTE will derived from the scalar subselect.
•Need to refer the same table multiple times in the same query.
•Readability will increase and maintenance of the complex queries very easily.

Examples of CTE:

WITH CTE AS
(
select id,city,SNAME, row_number() over(partition by SNAME order by id desc)
duplicate_id from#TEMP
)
delete CTE where duplicate_id>1
select * from #temp

In above expression I have write a comman table expression for delete duplicate data from
selected table .
In This article I have explain what is CTE (comman table expression ),uses of CTE and example
of CTE.I hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion.
,Thanks

Related Posts::

Rank(),dence_rank(),row_number() functions in sql server
cursor in sql server

Wednesday, 29 May 2013

example of Cursor in sql server

Simple example of Cursor.

Hi ,
In this article we will see a how to use a cursor with some simple example. check a cursor indetails in my post Cursor Indetails

firstly we will see what is syntax of cursor in sql server.

Systax of Cursor::

step1: declaration
declare @Cursor_name Cursor
step2: set set
set @Cursor_name = Cursor for
Select id From table_name
--( You use your query statement)
step3: open cursor
open @Cursor_name
step4: Fetch Cursor
fetch next
From @Cursor_name into @ID
step5: process cursor
while @@FETCH_STATUS = 0
   begin
print '@@FETCH_STATUS'+convert(varchar,@@FETCH_STATUS)
-- you will write your statements here
fetch next
   From @Cursor_name into @ID
   end
step6: Close cursor
close @Cursor_name
step7: Deallocate Cursor
deallocate @Cursor_name

Example::
for your better understanding we will see a example of cursor you create a temp table and fill some data in it.by using below statement.

CREATE TABLE#Employee (ID int,emp_name varchar(50))
INSERT INTO #Employee (ID,emp_name) VALUES(1,'anukrushna')
INSERT INTO #Employee (ID,emp_name) VALUES(2,'krushna')
INSERT INTO #Employee (ID,emp_name) VALUES(3,'pranav')
INSERT INTO #Employee (ID,emp_name) VALUES(4,'aditya')
INSERT INTO #Employee (ID,emp_name) VALUES(5,'harshal')
INSERT INTO #Employee (ID,emp_name) VALUES(6,'sandip')
INSERT INTO #Employee (ID,emp_name) VALUES(7,'ajit')

Create one more table.

CREATE TABLE#Employee_bk (ID int,emp_name varchar(50))

Now we can write a cursor for fetch records from a #Employee table and fill it in #Employee back up table. Check a below query for achive this .

declare @ID int
declare @emp_name varchar(50)
declare @getEmployee CURSOR
set @getEmployee = CURSOR for
select ID,emp_name
from #Employee
open @getEmployee
fetch next
from @getEmployee into @ID,@emp_name
while @@FETCH_STATUS = 0
begin
begin try
insert into #Employee_bk (ID,emp_name)values (@ID,@emp_name+' backup')
print Convert(varchar, @ID) +' Inserted'
print @emp_name+' Inserted'
end try
begin catch
print 'Error in insert' +Convert(varchar, @ID)
print 'Error in insert' +@emp_name
end catch
fetch next
from @getEmployee into @ID,@emp_name
end
close @getEmployee
deallocate @getEmployee
select * from #Employee
select * from #Employee_bk

SUMMERY ::

In this article I try to explain how use cursor in sql server. I hope it is helpfull to you.I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Cursor in sql server

Cursor in sql.

Hi,
In this article we will discuss about a cursor in sql server,what is cursor ,use and disadvantages
of a cursor,types of cursor etc.

Click here For example of Cursor in sql server
What is Cursor object in sql server ?

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Difference between Cursor and sql statement ? or when we use a cursor?

Generally our sql statement or sql query process a records in bench and a Cursor is worked with data or records on row by row basis. so if we need to do something on on row by row basis then you can use Corsor.
E.g::
Some time we need to Update only one column of the all record in table,that time
we can use cursor.

There are two types of cursors in PL/SQL:

1) Implicit cursors ::

We can use a Implicit cursor when DML statement like Insert,Update ,Delete are executed, a Implicit Cursor also used with select statement that return just one row is executed.

1) Explicit cursors ::

We can use a Explicit cursors with SELECT statement that returns more than one row.
Even though the cursor stores multiple records, only one record can be processed at a time,which is
called as current row. When you fetch a row the current row position moves to next row.

Difference between Implicit cursors and Explicit cursors ?

Both implicit and explicit cursors have the same functionality, but they differ in the way they
are accessed.A Implicit cursor is hold only single record ,but in case of Explicit cursor it hold a multiple
records but it process a only one record at a time

disadvantages of cursor?

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs. You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Subtypes of Cursors

1.Static Cursors

A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable. You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source.

2.Dynamic Cursors

A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.

3.Forward Only Cursors

A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling.You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source. There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the datato the cursor lifetime. It is not sensitive to any changes to the data source. A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source
.

4.Keyset Driven Cursors::

A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset.
The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened.A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations.
By default keyset driven cursors are scrollable.

SUMMERY ::

In this article I try to explain the what is Cursor , types of Cursor in SQL Server,and what is drowbacks or disadvantage of cursor. I hope after reading this article you will be able to understand different types of cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Wednesday, 22 May 2013

Sql server- send sql table data via email using store procedure

Hi All,
In this article we will see how to send a sql table data via the mail body by using
sql store procedure.

Problem:

Some time we need to send a sql table records via a mailbody in html format.
e.g- Send daily activity of jr person to Sr.person etc

Solution::

We will see a step by step solution of this problem below.

step1 :
I start by creating a temporary table and populating it with some sample data

Create table #Temp
(
[Id] [int],
[Emp_Name] [varchar](128),
[salary] [int],
[department] [varchar](128)
)

Insert into #Temp
select 1,'SA',12390,'INDIA'
union all
select 2,'SA',7965,'INDIA'
union all
select 3,'SANDIP',7880,'INDIA'
union all
select 4,'GAIKWAD',12390,'INDIA'
union all
select 5,'SAGRIKA',7965,'INDIA'
union all
select 6,'SAVITA',7880,'INDIA'

step2 : by bellow statement we will select a data from a table in Html format.

declare @xmlnvarchar(MAX)
declare @body nvarchar(MAX)
set @xml = cast(( select [ID] AS 'td','',[Emp_Name] AS 'td','',
[salary] AS 'td','', department AS 'td'
from #Temp order by ID
for XML PATH('tr'), ELEMENTS ) ASnvarchar(MAX))
set @body ='<html><body><H3>Employee Info</H3>
<table border = 1>
<tr bgcolor="#FF0000">
<th> ID <th> Emp_Name </th> <th> salary </th> <th> department
</th></tr> '
set @body = @body + @xml +'</table></body></html>'
print @body

Once the HTML has been formatted I send the email using the system
stored procedure "sp_send_dbmail" found in the msdb database and finally I drop the
temporary table. A email sending part is bellow

exec msdb.dbo.sp_send_dbmail 'IMRAN' , @recipients = 'sandip.gaikwad@bbraun.com', @copy_recipients = '', @blind_copy_recipients='', @subject = 'DATA IN TABULAR FORMAT....' , @body = @body , @body_format = 'HTML'
drop table #Temp

final query:
   In order to use the code below, against your database table, you will have
   to replace the table name and column names of your table wherever necessary.
   Also, you will need to change the mail profile name and email address that you
   want to use.
Here is the sample code.

Create table #Temp
(
[Id] [int],
[Emp_Name] [varchar](128),
[salary] [int],
[department] [varchar](128)
)

Insert into #Temp
select 1,'SA',12390,'INDIA'

satish

Tuesday 25 June 2013

SQL Interview QUERIES

Thursday, 20 June 2013

Insert output of sql function to table

Insert store procedure output to sql table

Insert bulk data from view to sql table

bulk insert from one table to another table

Monday, 17 June 2013

pivot without aggregate function in sql server

loop in sql server

Get MonthWise Calender in sql server

temp table and temp variable in sql server

Friday, 14 June 2013

Group by in sql server

Thursday, 13 June 2013

Interview queries in sql server

Saturday, 1 June 2013

select duplicate records in sql server

update duplicate records in sql server

comman table expression(CTE) in sql server

Wednesday, 29 May 2013

example of Cursor in sql server

Simple example of Cursor.

Cursor in sql server

Cursor in sql.

Wednesday, 22 May 2013

Sql server- send sql table data via email using store procedure

No comments:

Post a Comment